Separating Numerical and Alpha Data in cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.
 
If it always separated from the text by a blank and is always at the
beginning of the string:

=LEFT(A1,FIND(" ",A1)-1)
 
You can use Data | Text-to-columns to split the data at each space.
Although this will give you 3 columns for your first two examples and
4 for the third example, it is easy enough to re-combine these columns
with:

=TRIM(C2&" "&D2&" "&E2)

copied down the column and then fix the values. Alternatively, you
could use a formula with LEFT and RIGHT in conjunction with SEARCH or
FIND looking for the position of the first space.

Hope this helps.

Pete
 
To split the string at the first space, try
=LEFT(A1,FIND(" ",A1)-1)
and
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

You'll get an error if there's no space in the string, but at a push you
could trap for that.
 
Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.
Click to expand...

A2: 123 Anywhere St
B2:
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))>LEN(A2),
"",LOOKUP(1E+307,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))))

C2: =TRIM(SUBSTITUTE(A2,B2,"",1))


--ron
 
Thanks all. I ended up with a combination of LEFT, FIND and MID funbctions
that seem to be working for my purposes OK.
 
Back
Top