Count and get position of spaces within a string
 

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.

Count and get position of spaces within a string
 

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

Terry wrote:

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

Count and get position of spaces within a string
 

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)

"Terry" wrote in message
...
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.

Count and get position of spaces within a string
 

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

"Bob Phillips" wrote:

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)

"Terry" wrote in message
...
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.

Count and get position of spaces within a string
 

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



"Dave Peterson" wrote:

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

Terry wrote:

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