Split Fuction Headache - Seperate Numbers & Text

[MrManBoy]

Hi All,

Issue: I am having issues with the following code to reach the desired outcome. I have had only some success with it in what I want it to do; Maybe I have misunderstood something

Dim LR As Long, i As Long, X As Variant
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("H" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next i

Example Data: The font in bold is what I am trying to achieve with the above code.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Column H[/TD]
[TD]Column I[/TD]
[TD]Column J[/TD]
[/TR]
[TR]
[TD]12345.HR Manager[/TD]
[TD]12345
[/TD]
[TD]HR Manager
[/TD]
[/TR]
[TR]
[TD]12345.HR Manager, Boss & CEO[/TD]
[TD]12345[/TD]
[TD]HR Manager, Boss & CEO
[/TD]
[/TR]
[TR]
[TD]246810.CFO & CIO[/TD]
[TD]246810
[/TD]
[TD]CFO & CIO
[/TD]
[/TR]
</tbody>[/TABLE]







Thanks in advance

 

[JoeMo]

The Split function has a second argument which tells VBA where you want the split(s) to occur. If you leave it out (as is the case in the code you posted) the default is a space character. Try:

X = Split(.Value, ".")

 

[ParamRay]

.
.

Need to change the line to this:

X = Split(.Value, ".")

 

[WarPigl3t]

Pretty sure Joe figured it out for you, but just in case that doesn't work. I'll help you out. I'm not familiar with "With" loops so here is my version

LR = Range("H" & Rows.Count).End(xlUp).Row
i = 1
Do Until i > LR
    x = SPLIT(Range("H" & i).Value, ".")
    Range("I" & i).Value = x(0)
    Range("J" & i).Value = x(1)
    i = i + 1
Loop

 

[Rick Rothstein]

This is how I would write it...

Sub SplitAtFirstDot()
  Dim Cell As Range
   For Each Cell In Range("H1", Cells(Rows.Count, "H").End(xlUp))
     Cell.Offset(, 1).Resize(, 2) = Split(Cell.Value, ".", [B][COLOR=#ff0000]2[/COLOR][/B])
   Next
 End Sub

Note the red highlight optional argument in the Split function... it forces the array to only have two elements (protects against the text portion of the cell containing dots of its own).

 

[MrManBoy]

Thank you all for providing assistance

Codes work as to what I've been trying to achieve, you guys are bloody **** great, have no idea what this means *violins starts playing* haha anyways thanks fellas!

Hoo Roo!