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.

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

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" <(E-Mail Removed)> wrote in message
news:F61E7D66-E5F4-477B-AF50-(E-Mail Removed)...
> 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.
>

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" <(E-Mail Removed)> wrote in message
> news:F61E7D66-E5F4-477B-AF50-(E-Mail Removed)...
> > 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.
> >
>
>
>

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
>