Need a Microsoft Excel expert?

Question

I have a large list of contacts in excel (well over 3000). The Adress info for each contact is all on one cell, like this:

123 Anywhere Street, New York, NY, 12345

What i need though is the street in one cell, the city in another cell, the state in another, and the zip code in another.

Just wondering if there was anyway in excel to seperate it like this without going through each Address and manually doing it. Thanks in Advance

Answer 1

If they are separated with commas..................

Select the entire column

Go to Data

Text to column

Select "Delimited"

Click "next"

Put a comma in the "Other" box

Ok

That should separate your addresses as you want.

But without looking at the sheet, I cannot guarantee you that this method will do it.

If you need more info, add details or email me.

Answer 2

Do not look toward Microsoft to help. I have gone through this the hard way. Of course you will fumble around in order to learn. May I suggest you purchase the Excel 2003 for Dummies reference book. It does not come in an easy reading child's version BUT it is probably the best around ($21.99). Also, I bought an inexpensive set of CDs for $19.99: Instant Office XP & Windows, with 6 CDs, Deluxe Edition. Put out by Topics Entertainment, Renton WA. Has instruction CDs for Word, Excel, PowerPoint, Access, Outlook and Windows. It is not the greatest but certainly better than nothing and a good way to get started. Think I got it at a BEST BUY or CompUSA. Good luck!

Answer 3

Listen to Chaminda. You want to use the Text to Columns method he talked about. Just go to:

Data > Text to Columns...

Then you'll choose Delimited and unselect everything except for Comma. You'll want Comma checked. What this will do is it will separate your data in to separate columns based on where the columns are. So for your example it would separate it into four columns.

123 Anywhere Street
New York
NY
12345

You may have problems arise if the addresses don't have commas where they need them. Hopefully, that is just a minute number and you won't have to fix many. You can also use that same technique to separate by spaces in the text or by any other character.

One IMPORTANT note, make sure you clear out columnt to the right of the column. It moves the data over into the columns to the right. So if you have data immediately to the right if you aren't careful you could overwrite it.

Answer 4

1. Save excel sheet as a text file.
2. Then right click on the text file and select Open With - Microsoft Excel.
3. Select comma delimited when prompted.
Excel will then open the file just the way you want it - with each part of the address in a separate cell.

Answer 5

If you don't mind some extra columns and it's not too forgiving (additional commas will throw it off)

Cell A1 is
123 Anywhere Street, New York, NY, 12345

Cell B1 (Start of City)
=FIND(",",A1)

Cell C1 (Start of State)
=FIND(",",A1,B1 + 1)

Cell D1 (Start of Zip Code)
=FIND(",",A1,C1 + 1)

Cell E1 (Address)
=TRIM(MID(A1,1,B1-1))

Cell F1 (City)
=TRIM(MID(A1,B1+1,C1-B1-1))

Cell G1(state)
=TRIM(MID(A1,C1+1,D1-C1-1))

Cell H1 (zip)
=TRIM(MID(A1,D1+1,10))


Just place the formulas in a single row to the right of the address (you may have to change it a little relative to your worksheet)

A Copy of the resulting columns and Edit, Paste Special, Values will get you the Text...

Answer 6

there is a demo video in excel to show you how to do it

open excel and type in -seperate text in cell - in the online help

Answer 7

I am not an EXPERT expert, but I use excel a lot; I know it rather intimately and I can't think of a way to do that =( sorry.

Answer 8

Let say you insert the address at Cell A2

The then insert the following formulas:

At B2: =LEFT(A2,FIND(",",A2,1)-1)
At C2: =RIGHT(A2,LEN(A2)-FIND(",",A2,1)-1)
At D2: =LEFT(C2,FIND(",",C2,1)-1)
At E2: =RIGHT(C2,LEN(C2)-FIND(",",C2,1)-1)
At F2: =LEFT(E2,FIND(",",E2,1)-1)
At G2: =RIGHT(E2,LEN(E2)-FIND(",",E2,1)-1)

From them, you will see B2 as Street, D2 as City, F2 as State and G2 as Zip code. With this formula, you can drap them across for a large address list.

Good luck!

Answer 9

ok u can use VBA code to make a macor that analyze the colomn and separate them.. contact me if u want i can help you make one..

Answer 10

can u send me the file, i ll have a look