I have a data sheet with about 3000 entries, each one containing an
address. Only problem is that it was brought in from another program and
when they did it it grouped the City and the street address. So, I have
one cell for each entry that looks like;

123 Easy Street London

all in one cell.

I would like to put London in another cell, so that I can sort by city
without soing each one individually. Any ideas?

thx,

A

Hello,

Assuming that your address is in cell A1, the formula below should do the
trick.

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))
--
Hope this helps,
MarkN


"Jill" wrote:

I have a data sheet with about 3000 entries, each one containing an
address. Only problem is that it was brought in from another program and
when they did it it grouped the City and the street address. So, I have
one cell for each entry that looks like;

123 Easy Street London

all in one cell.

I would like to put London in another cell, so that I can sort by city
without soing each one individually. Any ideas?

thx,

A

Is there any kind of "character, (i.e. ,)" between the City and Street
address?
If there is on the Menu Bar - Select - DATA - Text to Columns and
follow the directions. If not you might try the following:
Select the column and Format ALL Cells with a COURIER FONT.
Select your column again and:
Do a Find and Replace: In the Find box - press the spacebar 2 times, In
the Replace press the spacebar 1 time. Do this as many times as
necessary to remove "ALL" extra spaces - you only want one space
between each of the 'words." You will know you have accomplished this
when you get a "NOT FOUND" message.
Now in the Find Box press the space bar 1 time.
In the Replace Box - press the the space bar 20 to 25 times, and select
replace ALL - this should put enough space between the "shortest and
longest" address:
Now do a "RIGHT JUSTIFY" on the Column. This should put everything
Lined flush right for the Cities. "Left Justify" might work better -
you will be able to determine in the next step.
Insert a blank column to the right of the above column. "New Column B"
With your column selected on the Menu Bar, Select, DATA - Text to
Columns, then select "Fixed Width" - click the NEXT. Now comes the
"trial and error."
Remove all breaks, except the one by the cities. Move or place it in a
position before the cities and then scroll down as necessary to view
how your column will break. If you are satisified select NEXT and then
FINISH.
Now in a "blank column (i.e. C) enter the following formula: =TRIM(B1)
and copy down, this will remove all of the extra spaces. Then copy all
of column C and do a Paste - Special - "Values" - then do the same for
column A.
Might be an easier way - but I don't know of it.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=513501

The following formula, which will be found in this newsgroup from time
to time, will extraqct the last word of a multi-word entry, separated
by spaces. It assumes the entry is in E3:

=MID(E3,FIND(CHAR(1),SUBSTITUTE(E3,"
",CHAR(1),LEN(E3)-LEN(SUBSTITUTE(E3," ",""))))+1,255)

The following formula will extract up to and excluding the last word:

=LEFT(E3,FIND(CHAR(1),SUBSTITUTE(E3,"
",CHAR(1),LEN(E3)-LEN(SUBSTITUTE(E3," ",""))))-1)

HTH
Kostis Vezerides

Jill wrote in
:

I have a data sheet with about 3000 entries, each one containing an
address. Only problem is that it was brought in from another program
and when they did it it grouped the City and the street address. So,
I have one cell for each entry that looks like;

123 Easy Street London

all in one cell.

I would like to put London in another cell, so that I can sort by city
without soing each one individually. Any ideas?

thx,

A



I've got it all sorted out guys; thanks for the help.

A