Help with Text to Columns


G

Guest

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!
 
Ad

Advertisements

G

Guest

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

Advertisements

S

Sandy Mann

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top