split street address into street no & street name
YAMAY
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
email...@email.com
Thanks a million!
Y.C. Wakefield
earl takasaki
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
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
addresses.
Earl Takasaki
>.
>
Hank Scorpio
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
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
again.
Y.C. Wakefield
Crash
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
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
--wes
David McRitchie
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
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
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
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.
Jon
Hank Scorpio
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
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
errors.
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:
=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
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...