Count and get position of spaces within a string

[Guest]

I have a columnn full of strings of varaible length and content which contain
0 to 4 spaces interspersed within the string
eg "1 1p 3 7c " or "2 4 1" or "1 2 x 4"
I want to extract the values between the spaces. If I know the number of
spaces and the position of each space I can use the LEFT and MID functions to
extract the values i want.
I am not fussed as to whether this is done within Excel sheet adjacent cells
or within a VBA procedure and returned to 4 adjacent cells.

 

[Dave Peterson]

Maybe you can select the column and do
Data|text to columns
delimited by spaces

 

[Bob Phillips]

Public Sub test()
Dim iLastRow As Long
Dim i As Long, j As Long
Dim ary

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
ary = Split(Cells(i, "A").Value, " ")
For j = LBound(ary) To UBound(ary)
Cells(i, j - LBound(ary) + 2) = ary(j)
Next j
Next i

End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Good solution did not know about the rows.count (.end etc ) or the split
function wow thanks very much

 

[Guest]

Also a good solution this is very useful as well as the solution by Bob
Phillips