How Do You .......

[Spike9458]

..... separate data in a column? Using MS Excel 2000, I have a database of
addresses that have the street number and the street name in the same cell.
For sorting purposes, I'd like to put the street number in its own column.
Is there a macro that can be run to do this? Is it doable?

Thanks,

--Jim

 

[Spike9458]

..... change all caps to proper capitilization? One of my databases has the
names in all caps. Is there a macro that can be run that will give the names
proper capitilization so it doesn't look like I'm yelling at the mailman or
the recipient?

Thanks,

--Jim

 

[Peo Sjoblom]

If the address are [street_number name_of_street] separated by a space you
can use 2
help columns, to get the number

=LEFT(A1,FIND(" ",A1)-1)

copy down as long as needed

to get the rest of the address (assume your help column with the above
formula is in B)

=TRIM(SUBSTITUTE(A1,B1,""))

so an address like

12345 Frogs Pond Avenue

will yield

B C
12345 Frogs Pond Avenue

 

[Andy Brown]

--Jim,

Insert a couple of blank columns after the street #/name column.

In the first column, use a formula like
=LEFT(E1,FIND(" ",E1)-1)
to get the numbers. In the second column,
=RIGHT(E1,LEN(E1)-FIND(" ",E1))
to get the names.

Copy these down as far as you need. Then copy & paste special (values) both
columns to "cement" them.

Of course there may be a few quirks in the format of some of the addresses
(eg: "Flat 12"), but it should do the bulk of the work.

HTH,
Andy

 

[Peo Sjoblom]

See

http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Andy Brown]

--Jim,

Use the PROPER function, eg: =PROPER(A1). See XL Help for info. Yelling at
the mailman is not advisable.

Rgds,
Andy

 

[Spike9458]

Hey guys, this is excellent. Exactly what I was looking for. Not only that
but the formula help you gave me gave me enough to experiment a little and
now I have the mailing list exactly the way I want it!

Thanks again, to you Andy Brown and Peo Sjoblom

--Jim