How 2 separate column of addresses into a colum of names and numb

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

Guest

Hi!

I would like advice on how to separate the names and the numbers of an
address written in one column into the name and the number in two separate
columns.
In otherwords "Kingstreet 23" must become "Kingstreet" and "23."

Can anyone help with a function that can do this?

Thanks in advance,

Rochelle
 
Hi Rochelle

if all your names & numbers are similar to your example, then data / text
to columns will probably work for you

ensure that you have a couple of blank columns to the right of your current
column (note, this process will split up your original column into two and
won't retain a copy of the original - if you want a "combined" column, copy
this column to a new column first).

select the column to be split up, choose data / text to columns, choose
deliminated choose NEXT, untick TAB, tick SPACE, click FINISH.
 
One way:

In place (e.g., A1 -> A1:B1):
Choose Data/Text to Columns. Click "Delimited". Click Next. Check the
Space checkbox, click Finish.

By formula:

A1: Kingstreet 23
B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1, FIND(" ",A1)+1, 255)
 
Thanks to both of you!!

Rochelle

JE McGimpsey said:
One way:

In place (e.g., A1 -> A1:B1):
Choose Data/Text to Columns. Click "Delimited". Click Next. Check the
Space checkbox, click Finish.

By formula:

A1: Kingstreet 23
B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1, FIND(" ",A1)+1, 255)
Click to expand...
 
Back
Top