Removing words from a string?
 

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP

Removing words from a string?
 

--Select the range/column that need to be converted. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard'. By default the
selection is 'Delimited'. Keep the selection and hit 'Next'. From the Step2
of the Wizard from the options check Space and hit Next.NextHit Finish

--If you are looking at a macro then try the below which works on active
sheet columnA

Sub Macro1()
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
arrData = Split(Range("A" & lngRow))
For lngcol = 0 To UBound(arrData)
Cells(lngRow, lngcol + 2) = arrData(lngcol)
Next
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"McP" wrote:

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP

Removing words from a string?
 

If all substrings separated by space(s) go to separate columns then use
DataText to columnsseparated by spaces!

Regards,
Stefi

€žMcP€� ezt Ã*rta:

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP

Removing words from a string?
 

Thanks you, both you and Stefi have opened my eyes to the text to columns
button. (would not have found that in a long time) Both of you have saved me
a stack of time. I will also try the macro. I still have work to do in lining
up address items, but these posts have been exceptionally helpful.
McP

"Jacob Skaria" wrote:


--Select the range/column that need to be converted. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard'. By default the
selection is 'Delimited'. Keep the selection and hit 'Next'. From the Step2
of the Wizard from the options check Space and hit Next.NextHit Finish

--If you are looking at a macro then try the below which works on active
sheet columnA

Sub Macro1()
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
arrData = Split(Range("A" & lngRow))
For lngcol = 0 To UBound(arrData)
Cells(lngRow, lngcol + 2) = arrData(lngcol)
Next
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"McP" wrote:

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP

Removing words from a string?
 

You are welcome! Thanks for the feedback!
Stefi

€žMcP€� ezt Ã*rta:

Thanks you, both you and Stefi have opened my eyes to the text to columns
button. (would not have found that in a long time) Both of you have saved me
a stack of time. I will also try the macro. I still have work to do in lining
up address items, but these posts have been exceptionally helpful.
McP

"Jacob Skaria" wrote:


--Select the range/column that need to be converted. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard'. By default the
selection is 'Delimited'. Keep the selection and hit 'Next'. From the Step2
of the Wizard from the options check Space and hit Next.NextHit Finish

--If you are looking at a macro then try the below which works on active
sheet columnA

Sub Macro1()
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
arrData = Split(Range("A" & lngRow))
For lngcol = 0 To UBound(arrData)
Cells(lngRow, lngcol + 2) = arrData(lngcol)
Next
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"McP" wrote:

I have a list of UK addresses in the format below, spaces separating, no
commas or other chars.

a1 = "1 high street leeds yorkshire"

I would like to be able to split the individual words/no.s out into cells of
their own, so that....

b1 = 1
b2 = High
b3 = street
b4 = leeds
b5 = yourshire

I can actually do some of this, eg =find("space char",a1) that will give me
pos 2
then I can do z1= LEFT(a1,2 -1) so pick the left number one char.

My problem comes in working out how long the string is (yes I know LEN, I
mean the substring from one space to the next space )

I want to be able to say, ok pos 2 is a space, where is the next one?
I then need to say the next part of the string is from pos 2 +1 to get the
character(if it is one), not the space, but I then need the length of b2 in
this instance, and so on until the end of the string.

This would possibly not be (too) fiddly if all the addresses were the same
length, and the addresses perhaps did not have multiple spaces at the end ( a
possibility, though I could concatenate an XX char so I know where it ends.

Can anyone suggest any subroutine code I could use in excel to do this simply?
Thanks in advance.
MP