*HELP* Street Address - How to break it up into multiple cells?

[jlipper]

Hello Excel users....

I am working in Excel 2000 with a simple spreadsheet.
I currently have a cell called Complete Address from a list of data w
purchased. In order to mail it properly, I need to remove the cit
from this cell and place it in a cell next to it.

Right now the cell has the street number, street name and city.
The city in every case is the last item in the cell and is preceeded b
a "space"

For example: 86 North Cleveland St Wilkes-Barre

What I would like is to be able to remove the word Wilkes-Barre an
place it in another cell. Another note, every city name is hyphenated
So I would need to take everything from the right of the cell up to th
first "Space", remove it and place it in a seperate cell.

Can anybody help me? I'm sure this is a pretty basic and eas
question......I just can't get the "paperclip" to give me the correc
answer!!!

I attached a sample of the worksheet.

Please e-mail me the answer if possible - but I will try to kee
reviewing this forum

Thanks in advance - Joel
(e-mail address removed)

Attachment filename: sample-data.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65798

 

[Lady Layla]

Have you tried highlighting the columns and then using Data - Text to columns?


: Hello Excel users....
:
: I am working in Excel 2000 with a simple spreadsheet.
: I currently have a cell called Complete Address from a list of data we
: purchased. In order to mail it properly, I need to remove the city
: from this cell and place it in a cell next to it.
:
: Right now the cell has the street number, street name and city.
: The city in every case is the last item in the cell and is preceeded by
: a "space"
:
: For example: 86 North Cleveland St Wilkes-Barre
:
: What I would like is to be able to remove the word Wilkes-Barre and
: place it in another cell. Another note, every city name is hyphenated.
: So I would need to take everything from the right of the cell up to the
: first "Space", remove it and place it in a seperate cell.
:
: Can anybody help me? I'm sure this is a pretty basic and easy
: question......I just can't get the "paperclip" to give me the correct
: answer!!!
:
: I attached a sample of the worksheet.
:
: Please e-mail me the answer if possible - but I will try to keep
: reviewing this forum
:
: Thanks in advance - Joel
: (e-mail address removed)
:
: Attachment filename: sample-data.xls
: Download attachment: http://www.excelforum.com/attachment.php?postid=657982
: ---
:
:

 

[JE McGimpsey]

Unless you have fixed field lengths (in which case Data/Text to Columns
is the way to go), I'd suggest splitting the cell into two columns:

A1: 86 North Cleveland St Wilkes-Barre
B1: =LEFT(A1,FIND("$",SUBSTITUTE(A1,"
","$",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
C1: =MID(A1,LEN(B1)+2,255)

You can then copy columns B:C, Paste/Special/Values, then delete column
A.