Excel 2003 Split cells

  • Thread starter Thread starter Brent
  • Start date Start date
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!
 
I use Excel 2000, but I imagine it more or less the same
function. Select the column you want to split in two.
Make sure you have an empty column to the right of it.
Click on Data/Text to columns.../Delimited/Next/...select
your delimiter, which is likely "space".../...you will
then see your text separated.../click Next/click Finish.

Hope this helps.

Connie
 
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
 
The answer I sent you I don't think will really work since
you are separating unevenly spaced words and numbers. If
they were aligned evenly, Data/Text to columns/FIXED
WITH... would work, but that isn't likely your case here.

Connie
 
This is interesting. How do you then delete all the
numbers in the first column? They copy over to B column
but remain in A column.

Connie
 
Why would he delete the numbers only, if he extract the numbers in the next
column,
then use a third and remove the numbers, first select the extracted numbers
and copy and
paste as values in place. Then use =SUBSTITUTE(A1,B1,"") substitute the
number in B1 with nothing will return the address without number, copy and
paste special as values in place and finally delete the original column..
 
Back
Top