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

split street address into street no & street name

3 views

YAMAY

Feb 17, 2002, 12:37:52 AM2/17/02
to

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?

email...@email.com

Thanks a million!

Y.C. Wakefield

earl takasaki

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

column A starting with A1. In columns B1 and C1 put
respectively:

=LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),1)-1)
and
=RIGHT(TRIM(A1),LEN(TRIM(A1))-SEARCH(" ",TRIM(A1),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
to

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
apoll...@ozemail.com.au Change XVIII to 18 for real address.

YAMAY

Feb 17, 2002, 2:22:55 AM2/17/02
to
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
email...@email.cojm

YAMAY

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

Y.C. Wakefield

Crash

Feb 17, 2002, 3:27:02 AM2/17/02
to
>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

Jon

wes

Feb 17, 2002, 4:08:56 AM2/17/02
to
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

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
--wes

David McRitchie

Feb 17, 2002, 9:24:39 AM2/17/02
to
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.

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
http://www.mvps.org/dmcritchie/excel/join.htm

HTH, (see getstarted.htm for instructions to install a macro)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"YAMAY" <email...@email.com> wrote in message news:3c6f3f5d...@news.earthlink.net...

Peo Sjoblom

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

if that's the case then this should work

=LEFT(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))),SEARCH(" ",TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))-1)

for street numbers and

=RIGHT(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))),LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))-SEARCH("
",TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))))

for the names

--
Regards,

Peo Sjoblom

ROT13 email

"Crash" <jonc...@NOTrogers.com> wrote in message news:3C6F695B...@NOTrogers.com...

Crash

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

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

Jon

Hank Scorpio

Feb 18, 2002, 4:38:04 PM2/18/02
to
On Sun, 17 Feb 2002 05:37:52 GMT, email...@email.com (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:
=RIGHT(TRIM(A1),LEN(TRIM(A1))-SEARCH(" ",TRIM(A1),1))

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

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

The reason for this is that the spaces in the data were not
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:
=LEFT(TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))),SEARCH("
",TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))),1)-1)

To return the remainder of the address, the formula needs to be:
=RIGHT(TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))),LEN(TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))))-SEARCH("
",TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))),1))

[Copy sent by E-Mail]

Hank Scorpio

Feb 18, 2002, 4:43:20 PM2/18/02
to
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
>moment.)

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

0 new messages