Adding spaces between numbers in a cell that contains a full address

  • Thread starter Thread starter chunt
  • Start date Start date
C

chunt

Hello,

Sample data:

3-764 Neighbourhood Cir
39 Frostbite Lane
3938 Stardust Drive
4 Jones Lane

I have a spreadsheet that contains addresses in a column such as the
ones above. What I need to do is separate the numbers in the addresses
in one of two ways. Either would be ok.

The first is (and better for me) would be so that the number portion of
the address would have spaces between every number, and then the rest
of the addresses in the same cell. For example, "3-764 Neighbourhood
Cir", would become "3 - 7 6 4 Neighbourhood Cir". The second would be
to split the number portion away from the street portion into separate
cells, to which I could then add the spaces between numbers.

One of the problems is of course that the number portion is variable in
length. If anyone could help me, I'd be very grateful.

Thanks a lot :)

Chris.
 
You can use Data/Text to columns using a space as the delimiter, or 2
formulas:
=left(A1,FIND(" ",A1)-1
and
=MID(A1,FIND(" ",A1)+1,255)
Bob Umlas
Excel MVP
 
Back
Top