Excel 2003 Split cells

B

Brent

Hello I hope one of you excel gurus can help!

I have the following data in a cell


Blackstone Avenue 300
Freemont Street 212


I want to bulk split the number from the letters to get 2 cells.. One cell
with the street name and one cell with the address.. Any help is
appreciated!
 
C

Cesar Zapata

Brent,

Lets say your value is in A1
then

use this in B1
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))


then fill down

regards,

Cesar Zapata
 
J

Joe Bloggs

Brent

Try Text to columns in the Data Menu. This will
Automatically split the two lines into six columns (using
a space separator)

If each address has three parts you can delete two columns
in each line by double clicking on the separators this
will give you two columns for each line. Be sure to have
an empty columns to place your second line.

you then have to figure how to move the second line to
where you want it - a macro would do it quickly but I
don't know where to place it.

Peter Atherton
[email protected]
 
Top