Text to columns, split at first space only

  • Thread starter Thread starter Wowbagger
  • Start date Start date
W

Wowbagger

I have a 2500 line column of street addresses along the lines of (123 E
Grand Maple). How can I split this column into two columns, first column
only the numeric portion, second column only the street portion even though
the street portion may contain spaces?

For simplicity's sake I can assume that the first space in the address
represents where the split should occur and that any subsequent spaces
should be ignored.
 
Assuming the addresses start in cell A2

=LEFT(A2,FIND(" ",A2)-1)

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

And drag down ...

Regards

Trevor
 
I have a similar problem however I have data that has 2 spaces and
need the numeral at the end. With the formula above I get the word an
numeral.

ie
cell = word word: number

need result
number

I had tried RIGHT(B143,FIND(" ",B143)-9) however the number is no
always the same number of digits and it sometimes gives a resul
including part of the text before it, or not all of the number
 
Try:

=VALUE(RIGHT(B143,LEN(B143)-1-FIND(" ",B143)))

Regards

Trevor


"Aussie_Striker"
 
Back
Top