Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Splitting Cells With Commas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have inherited a spreadsheet listing clients' addresses. Each client's full address is written in just one cell and separated by commas (e.g. 1 High Street, Brighton, East Sussex, BN1 1AB). What I would like is for each component of the address (i.e. 1 High Street) to be listed in its own individual cell. Is there any easy way to do this, as I'm a bit stumped at the moment!

Thank you

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Output the Name of the Current Excel Workbook Including Extension - UDF
- Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics


I have a worksheet full of text such as:

1 The High Street, Anytown, Anywhere, ZIP

There may be three, four or five elements to the address which are all
separated by commas. I would like to separate these into individual elements
(so I can import them to Access). I have the functions to strip out the first
and last component but I'm really struggling with the otheres.

Any help gratefully accepted.

If nothing changes, everything stays the same

I have an issue with a lookup formula. I am trying to create a sheet where a front end user can input a Colum or two of their location (ie. Street and number) and it return the further information, to provide a code to them they would not know. My issue is as there is no unique identifier im getting confused.

As per the example belwo (cant seem to attach a file?! maybe as new user?) I want people to be able to input as little as possible to return the full row of information.






Main Street




Main Street




Main Street




High Street




High Street




High Street




Queens Stree




Queens Stree




Queens Stree




Kings Street




Kings Street




Kings Street



E.G. I live at 1, main street, west. But I cant simply input 1 as there are two addresses with a 1 that would appear or just main street as three address match this, and so on.

So can I get a function that will match 2 columns and return the whole row of information.

E.g. I type in that I live at 1 so it then suggests that this could pertain to Main Street or Queens Street but not the others as none have a number 1. Then I select which and it will then highlight to me that I am 1, main street, west.

(If that makes sense?!?)

Hope you can help!

I am a Website Developer and DBA Admin, I have been messing around in VBA for a few months now and decided to create a few functions to split a full address that is in one cell into multiple cells (Street, City, State, Zip, Zip + 4)

Here is a example:

Please Login or Register  to view this content.

So far all my code is still under development. It is in no way near perfect or complete yet.

I am looking to get user feedback and tips on how to make it simpler. I have not seen any one come up with some functions to do this process. All solutions I have seen are based off if the address as commas. I gathered what I can from this site and Google to figure out how to put all this together. I am open to all thoughts and criticism.

READ NOTES: These functions are based on a few things;
1. The full address column must be all upper case.
2. Make sure there are no commas or hyphen. # is accepted as some addresses have apartment number, etc

Known Errors:
1. Addresses outside of United States turn up errors
2. Addresses without Street Abbreviation (ie: St, Dr, Loop, Ridge)
3. Addresses with random numbers lol i'm still working the bugs out.

Street - Place =street(a2) into column
City - Place =city(a2) into column
State - Place =state(a2) into column
Zip - Place =zip(a2) into column
Zip + 4 - Place =zip4(a2) into column

Code will be posted in next post...

I know this should be really easy, but I can't figure it out. I have a list of 400 addresses that my client wants in numberical order from lowest address (3 Street) to highest address (1001 street).

When I hit sort, it sorts them by 1's, then 2's, then 3's, etc. How can I get it to recognize that 3 Street is lower than 1001 Street?


Hi, Hope someone can help.

My aim is to compile a mailing list so that only one letter is sent to each address. The mailing list should contain both the customer's name and address.

Starting at N2 down is a list of addresses. In M2 down is a list of customer names. I need a formula that will look down the addresses and match it to the first name ONLY at that address, eliminating all others. So for example the expected output for the following data:

Fred Bloggs...23,Main Street
Jim Bloggs ... 23,Main Street
Bob Bloggs ..23,Main Street
Paul Hobb ...14,High Street
Mat Brown... 16, Railway Road
Nic Brown ...16, Railway Road

should be

Fred Bloggs...23,Main Street [Jim & Bob Bloggs ignored as at same address]
Paul Hobb ...14,High Street [this is a unique address and so is included]
Mat Brown... 16, Railway Road [Nic Brown ignored as at same address]

Basically a list eliminating duplicate addresses. A formula I can copy down would be ideal.

Many thanks in advance.

I have the following problem which I hope someone can please help me with!

I have an address field in my spreadsheet as one cell e.g.

15 High Street, London, W1T 6YH

However, I need the cell split into separate cells (i.e. street, town, postcode), and I was wondering if there is any way that Excel can be told to split the cell each time it find a , ?

Many thanks for your help.

Best wishes


Good Evening Everyone,

I have read many of the threads here and am amazed at the information presented. I finally decided to register and post the challenge I am working thru.

Every Week I get a sheet from Corporate with over 5,000 clients, only 1,000 of those are mine. I want to update the information contained in the Corporate sheet to My Sheet, so that I don't have to sort my clients out each time and can keep my notes. Each Client has a Unique Client Id (100, 101, 102 etc). (see example below)

I can move the columns around a bit in both sheets but other than that as the sheet comes from corporate things are locked. Column headings and client ID's are a constant.

I'm beating my head against the wall trying to compose a IF THEN or a macro. Any ideas?

Thanks Everyone!!

Corporate Sheet:
100 John Main Street
101 Paul Smart Street
102 Ethan Simple Street
103 Nathan Near Street

My Sheet:
100 John Carp Street Good Client
101 Paul Smart Street Leaving Soon
103 Nathan Sunny Street Moved

so using this example I want MY SHEET and my specific clients information to be updated from the Corporate Sheet and keeping my notes.

i have been looking for a way to put a space between the street number and address and then move the street number and address to seaparate colums and i come up with nothing pertaining to my situation. i need some serious help.

i attached the excel sheet with a few address from a much larger workbook of 4300 addresses. as you see i am working on a big project, so i would greatly appreciate some feedback

I am working on a home PC, Excel 2003. I have a column (it is column C of A
thru K, if that matters) of data that includes street address (may include
suite number, etc.), city, state, zip and all info is separated by commas
except state and zip. I am trying to separate the information in this column
so that each part is in its own column. I need to keep all of the address
(street number, street name, PO box number, suite number, etc. together) in
one field, city in the next field, then state, then zip. The problem I am
having when converting text to columns using the comma as the delimiter is
that I end up with too many columns of data. Is there a way to do something
like this starting with the comma farthest to the right and only going back
five characters to get the zip out and then continuing from right to left for
two commas to strip the state out then the city, leaving the rest to be the

Any help is greatly appreciated. My only alternative is to retype all info
in columns A through K, rows three through 1,102.
God Bless! Cindy

I am currently a police officer in the UK and I am trying to device a
spreadsheet / database that will highlight problem hotspots.

Now, I intend to create a spreadsheet that will highlight areas that are
affected by different crime. So, in one sheet I will have burglaries, car
crime in another, antisocial behaviour in another.

Now I am unsure what data the spreadsheet should contain and how to display
the results. I would like the results to come back on an individual name /
address basis per sheet in the file, but I would also like it to produce
results on road names.

So for example, if Mr Jones lives in 123 High Street, I would want the
spreadsheet to highlight it if Mr Jones gets burgled on two or more
occasions. Nonetheless, I would also like some results produced if two or
more burglaries are recored in High Street, regardless of the house number.

How would I go about this? Can I search cells in a column for specific
words, so in the above case, "High Street" regardless of number?

Many thanks

I've inherited a workbook with data like:

A1 = 1 The Street B1 = Anytown

In C1 if I use A1&" "&B1 I get 1 The Street Anytown

What I need in C1 is:

1 The Street

Other info - The example shows just two of seven address lines that need to be concatenated into one cell. There are approximately 200 addresses to be manipulated.



If I have a column full of email addresses in Excel 2007 and I want to copy that column and paste it into a website that only accepts the email addresses if they are all separated by commas how do I do that without manually adding commas between each email address?



Here is the basic situation. I have about 10 excel sheets containing addresses of thousands of contacts. They are written like this:
[Prior Title][First Name][Middle Name][Last Name][End Title][Street Address Line 1][Street Address Line 2][Street Address Line 3][City, State Zip]

Not all cells, like titles, contain data for all persons. Anyway, what I need is a way to insert, automatically, these addresses into the proper address space in a prewritten letter and on an envelope. I know how to merge to create address labels in word, but not merge them into a completed letter, as in:

Word File:
December 7, 2005

[Prior Title][First Name][Middle Name][Last Name][End Title]
[Street Address Line 1]
[Street Address Line 2]
[Street Address Line 3]
[City, State Zip]

Dear [Prior Title][First Name][Last Name][End Title]

How are you, blah, blah, important stuff, blah.



So, one letter, thousands of different addresses. What do you folks think I can do to achieve this? Any suggestions, except for how to make normal address labels, would be greatly appreciated.



I've been dealing with this headache for over 2 hours now, and I was hoping maybe some of you can help me out.

I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.

So in the end it would go from: (sorry for the periods.. extra spacing didn't work!)
AAA...123 Street...City...<blank>
BBB...456 Street...City...<blank>
CCC...789 Street...City...<blank>

AAA...123 Street...City...1
AAA...123 Street...City...2
AAA...123 Street...City...3~
AAA...123 Street...City...150
BBB...456 Street...City...1
BBB...456 Street...City...2
BBB...456 Street...City...3~
BBB...456 Street...City...150
CCC...789 Street...City...1
CCC...789 Street...City...2
CCC...789 Street...City...3~
CCC...789 Street...City...150

I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it

I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.

Hopefully I made a little bit of sense of what I'm trying to do/achieve..

Thanks ahead of time!

I am working on a mailing list and when splitting addresses into two or 3 columns, occasionally I'm left with a trailing comma. I'm trying to figure out a macro to remove trailing commas without removing commas from the rest of the cell.


Original Content
Cell A1: 12345 1st Street, Bldg 4, 6th Floor, Room 610

New Content
Cell A1: 12345 1st Street,
Cell B1: Bldg 4, 6th Floor,
Cell C1: Suite 610

I don't want Cell B1 to lose the comma between Bldg 4 and 6th Floor, but Cell A1 and Cell B1 should lose the comma at the end. I am going to need to do this to all highlighted cells, not just Cells A1 and B1 since this is a huge mailing list. This would apply to all highlighted cells, not the entire sheet.

My thought is that the first step is to trim to remove any trailing spaces if it shows as [Street, ] rather than [Street,], but i'm not sure what the rest of the code should be to remove the comma at the end (or a slash if that is there instead of a comma).

Thank you for any help you can provide.

Hi - I'm running 2007, and have a mailing list that is setup - A1 = text(full name of company), A2 = text(full street address, city, state, zip) NO PUNCTUATION.

I have been able to separate each word for the address line, but this leaves me with many different length addresses spread out over different cells, so taking just the city state and zip is not feasible in large numbers. I have over 10,000 of these listed down column A1.

I need to be able to extract city, state, and zip, (There is no punctuation in the address line)and put them all in one cell, for the third line of the address box. I need them to cut from the source, also - leaving the source only the street address.

I would greatly appreciate any help on this. Thanks


I have a list of names and addresses as follows:

700 Main Street

I would like to parse the information with street name
( Main Street ) in one field and street address ( 700 )
in a separate field.

Street number and street name are varied in length
throughout the list.

Is it possible to parse everything to the left or right of
the space?


Hi Experts,

I have list of addresses, which contains Address line1,Addr Line2(optional),Addr Line3(optional),Town,Postal Code.

I want to extract all data except Postal Code.
My Addresses are like

5 Bridge Street, Wisbech, PE13 1AF
81 High Street, Holbeach, Spalding, PE12 7ED
The Old Vicarage, Capel Bangor, Aberystwyth, SY23 1LZ
85 Uxbridge Road, London, W5 5TH
Marringdean Road, Billingshurst, RH14 9HD

i tried With
=TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))) which returns only first text,
and here no of commas are not fixed

How can i do this ?


I have a spreadsheet containing hundreds of rows of info like this:
MICKEY MOUSE 23 Smith Street, Bolton, Exeter CH45 1LP

How can I split it up into 5 or more columns to look like this:
Name Address 1 Address 2 Address 3 Address 4 MICKEY MOUSE 23 Smith Street Bolton Lancashire CH45 1LP
(I dont need the titles row eg "address 1" in the result by the way)
I notice there are always 2 spaces in the original cell, after the name and before the address, if this helps at all. Any suggestions?

I have a spreadsheet that I download each day that contains a column of addresses (hundreds of entries). This column contains the street number and street name in the same cell (i.e. 123 Main, 456 Hampton, etc.). Then in a separate sheet I have a static list of just street names (i.e. Hampton, Southfield, Mayfair, etc.). I'm needing an easy automated way to compare these daily.

I need something to look to the static list of street names, then look at the column of addresses and see if any of those addresses 'contain' one of the street names from the static list, and if so highlight the address.

Does anyone have any ideas?

Thank you!

164 Canal Street, NY, NY 10013

The information listed is in once cell and i need to separate it into cells for street address, city, state, zip.

I have done it in the past and cannot rememeber. I remmber starting the formula with =Left( and =Right(

but for the life of me I cannot remember. Please help ASAP! Also I need it to work over multiple cells. so basically I need it to pull out the first info up to the comma. Then a forumal to start at comma and pull out the info up to the next comma, etc

Thank you so much

Hello all,

It's been a long time since my last post. I am hoping someone can help me with the following question. I have an excel spreadsheet database displaying 5.000 contact information such as my example below:

Title FirstName LastName Address
Mr adulted it is me 144 picton street e
Ms Moe Scally 1343 university court

What I am trying to do is put 144 in its own column to the left of address and the street name (picton street e) in its own column or the street name to the right of the address column.

Or as in the second example What I am trying to do is put 1343 in its own column to the left of address and the street name (university court) in its own column or the street name to the right of the address column.

In simple terms, this 5,000 enrties need to be sorted by street name only, exluding numbers, possible PO Box, or RR # 3, etc...

Thanks in advance,

Hi there,

I've about 50,000 rows of data and an address in every row thats been entered into a single cell by using Alt+Ent

Sample of cell

3 Sample Street
Sample Town

(Postcode is already split into own column)

The number of lines in each address varies and there are no commas etc.

Cheers, Rich

I have a spreadsheet that has about 100 tabs. Each tab represents one person and their personal info, i.e. Name, Address, Phone Number... What I need to do is create a new spreadsheet that contains everyones Name and Address on one tab.

Currently, the data is like this, one person per tab:

2 First Last
3 Street Address
4 City, State Zip

What I need to turn it into is this, with everyone on one tab.

2 First Last Street Address City, State Zip
3 First Last Street Address City, State Zip
4 First Last Street Address City, State Zip
5 First Last Street Address City, State Zip
6 First Last Street Address City, State Zip

Is there a way that I can run a macro to move from tab to tab and pull the data from the required fields?


Hi there

I have inherited in a new job a spreadsheet that is basically a Suppliers Database.
The address when you look at it goes from left to right - after the company name there are 2 bold vertical lines, same after the street name,town,postcode etc.
When you click on this cell the data then looks like an address label ie name on one line, street name on the next etc.

How Do I do This??? What are these vertical lines???

Any help much appreciated.