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

[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.

 

[Bob Umlas]

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

 

[jpdphd]

Chris,

Here is a solution based on multiple instances of the SUBSTITUTE
function. It is easier to show an example rather than to type it all
out here.

http://homepage.mac.com/jdilger/Excel/substitute.jpg

Let me know if it is not clear.

jpdphd

 

[chunt]

jpdphd,

Thanks a lot This did what I needed.

Cheers,
Chris.

 

[jpdphd]

Great! Happy to help.