Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

split street address into street no & street name

Skip to first unread message


Feb 17, 2002, 12:37:52 AM2/17/02
Someone please help! I'm about to pull all my hair out and going nuts.

We have a large spreadsheet with the street addresses (numbers +
names) in one column and I have to split the column into two columns,
one with the street numbers only and the other with the street names
only, so we could sort them by the street name.

Is there a way to do it without having to go through each cell and
split them manually?

Your help would be greatly appreicated. My e-mail address is

Thanks a million!

Y.C. Wakefield

earl takasaki

Feb 17, 2002, 1:10:01 AM2/17/02
Okay. Let assume that there is a space character between
the number part and the street part.

Let's assume further that all of your addresses are in
column A starting with A1. In columns B1 and C1 put

=LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),1)-1)

then copy down columns B and C as needed. This should do
the trick for the vast majority if not all standard

Earl Takasaki


Hank Scorpio

Feb 17, 2002, 1:40:34 AM2/17/02

There are a couple of options, but probably the easiest (assuming that
there's a space between the number and street name, which seems
reasonable) is to go to the Data menu and use Text To Columns. Select
a delimited split, and split on spaces. This will break up each entry
into separate columns whenever a space is encountered. You may need to
tweak a couple of the entries.

Alternatively if you don't want to split up the street name component
you could use a couple of formulas which do the same thing, but only
for the first space encountered. Say the entries are in column A. In
column B you could have:
=LEFT(A1,FIND(" ",A1,1)-1) to get the number, and in column C:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) to get the rest of the address
including the street.

Hank Scorpio Change XVIII to 18 for real address.


Feb 17, 2002, 2:22:55 AM2/17/02
Thanks so much for your responses and both solutions work well on the
address I entered, but for some reason, they did not want to work on
the cells copied into the spreadsheet from the table on our web page.
This is really wierd.

Would you be able to take a look at the attachment. It's just a small
sample of the real file with no macro or virus to worry about.

Thanks again!

Y.C. Wakefield


Feb 17, 2002, 2:25:24 AM2/17/02
sorry! I guess the attachment wasn't sent correctly, let me try it

Y.C. Wakefield


Feb 17, 2002, 3:27:02 AM2/17/02
>copied into the spreadsheet from the table on our web page.

There you go, I will bet you that the spaces aren't spaces (20hex, 32dec)
their No-Break spaces (A0hex, 160dec).

Your can check this out with

=CODE(MID(A1,space position,1))

32 is a space
160 no-break space



Feb 17, 2002, 4:08:56 AM2/17/02
i have had to do this many times (split one cell into two
cells). The easiest way i have found is to simply
highlight the column that you want to split, then copy it
into notepad (to remove formatting), then copy it from
notepad into ms-word.

now you can use the find and replace feature in word
(ctrl+H). search for all the spaces (just hit the spacebar
key once in this entry box), and have them be replaced
with a tab character (type: ^t in the entry box). then
click on "replace all" and you are done (with some minor
cleanup due to multiple spacing and etc...)

if you only want to use this new column to sort with, then
i would keep the original column to use for things like
labels, envelopes, payroll, etc. and only use the new
split columns for sorting. if you do it this way, then you
can simply remove the number portion of the cells.

to remove the number portion, do this: when you find and
replace in word, click on "more" and use the "special" tab
to have it look for "any digit". then just leave
the "replace with" entry box blank, and it will delete all
the digits but leave the letters. it might even be easier
to do it this way again but this next time have it search
for "any letter" and delete those. now you should have two
new columns of data -- one for numbers and one for letters.

be sure to copy back into notepad to remove the ms-word
formatting before you copy into excel.

i suppose this method is not as precise as others have
suggested, but it really does work.
good luck

David McRitchie

Feb 17, 2002, 9:24:39 AM2/17/02
Hello Yamay,
Please do not include attachments they are not needed, and
should not be included when posting or responding in text
newsgroups (non binary newsgoups). Including an attachment
will only DELAY most responses as fewer people will look at
the attachment or even any posting with an attachment.

I'm sure we all understand street addresses (without reading yours)
1023 West Washington Ave
1023 1/2 West Washington Ave.
The second is not very common and won't be considered below.

It is easiest to split with a macro, and then once split you are
finished. Create a column to the right of the street portion then
use the SepTerm macro as found in
Rearranging Data in Columns

HTH, (see getstarted.htm for instructions to install a macro)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros:
Search Page:

"YAMAY" <> wrote in message

Peo Sjoblom

Feb 17, 2002, 9:29:43 AM2/17/02
Hi Jon,

if that's the case then this should work


for street numbers and


for the names


Peo Sjoblom

ROT13 email

"Crash" <> wrote in message


Feb 17, 2002, 1:58:03 PM2/17/02
Hi Peo,

I was correct, I downloaded YAMAY's sheet and looked at it with Hex WorkShop and they are No-Break Spaces.
And your formulas worked.


Hank Scorpio

Feb 18, 2002, 4:38:04 PM2/18/02
On Sun, 17 Feb 2002 05:37:52 GMT, (YAMAY) wrote:

The day before yesterday Yamay sent me a copy of a workbook which used
solutions recommended in this thread. (I didn't see it until this
morning since my computer currently resides on my dining room table
awaiting surgery, and my internet access is a tad intermittent at the
moment.) The short version is this; the formulas that were recommended
were returning #VALUE errors. He went for the formula format:

In column B, to return the number:
=LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),1)-1)

In column C, to return the remainder of the address:

The Trim is necessary as the list of addresses in column A have a
leading space.

Although the entry that he manually typed in in row 1 returned the
correct split, the data from the file that he needs to split returned

The reason for this is that the spaces in the data were not
conventional spaces. Instead, they had a CODE value of 160, which
under ASCII-ISO 8859-1 is a "non-breaking space". (Conventional spaces
have a code value of 32.) Unfortunately changing the search criterion
to =CHAR(160) won't help, because the TRIM function only trims out
conventional spaces. It's therefore necessary to modify the string
itself. This can be done through the SUBSTITUTE function.

To return the street number, the modified formula needs to be:

To return the remainder of the address, the formula needs to be:

[Copy sent by E-Mail]

Hank Scorpio

Feb 18, 2002, 4:43:20 PM2/18/02
On Mon, 18 Feb 2002 21:38:04 GMT, Apoll...@hates.spam (Hank
Scorpio) wrote:

>The day before yesterday Yamay sent me a copy of a workbook which used
>solutions recommended in this thread. (I didn't see it until this
>morning since my computer currently resides on my dining room table
>awaiting surgery, and my internet access is a tad intermittent at the

[Cough], hence the reason for me not noticing that someone else
answered this in a differently named thread a few spots down. Oh

0 new messages