I have a list of addresses. I want to separate the street number from the
street name. This is an obvious use of Date / Text to Columns.

My trouble is that I ONLY want to separate the number from the street name.
Instead, it separates EVERYTHING into separate columns. Like this:

12 Maple Street =
12 Maple Street
12595 S. 21st Avenue, 2nd Floor =
12595 S. 21st Avenue, 2nd Floor

Can anyone please advise me? Thanks!

if the street number is always first
try
=left(a1,find(" ",a1))
and
=right(a1,len(a1)-find(" ",a1))


"ClaireView" wrote:

I have a list of addresses. I want to separate the street number from the
street name. This is an obvious use of Date / Text to Columns.

My trouble is that I ONLY want to separate the number from the street name.
Instead, it separates EVERYTHING into separate columns. Like this:

12 Maple Street =
12 Maple Street
12595 S. 21st Avenue, 2nd Floor =
12595 S. 21st Avenue, 2nd Floor

Can anyone please advise me? Thanks!

There may be a better way but if you use Text to Columns and then in the
first column past ALL (ie Column G using your example). then data enter

=A1 for the number and in the next column again (ie Column H), the formula:

=TRIM(B1&" "&C1&" "&D1&" "&E1&" "&F1)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"ClaireView" wrote in message
...
I have a list of addresses. I want to separate the street number from the
street name. This is an obvious use of Date / Text to Columns.

My trouble is that I ONLY want to separate the number from the street
name.
Instead, it separates EVERYTHING into separate columns. Like this:

12 Maple Street =
12 Maple Street
12595 S. 21st Avenue, 2nd Floor =
12595 S. 21st Avenue, 2nd Floor

Can anyone please advise me? Thanks!