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 Tutorials

How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Link to Cells on Other Worksheets in Excel
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...
Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick
Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forenames, surnames and ages. As ...
Quickly Copy the Last Action to Multiple Cells in Excel
In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y can be used to do the same ...

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


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'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 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


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.



I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.

Any ideas?


I have been working on this all night and i've reached my limit...
Hopefully someone can help me with this....

I have a central list of address with St# and Street Names
Each of those addresses is associated with a Postal walk.
Often a particular street (Example: Main St) crosses into several postal walks
Thus 1-50 Main Street is Postal walk SS0011 while 51-100 Main street is Postal Walk SS0022 and so on...upto 4 times.

I have another data set on sheet2 that needs to search for the street address in sheet 1 and also search the range of Street #'s to determine which Postal Walk code to enter. Thus if the address in Sheet 2 is 74 Main Street, then it should return SS0022

Thanks for everyones help in advance

I'm trying to "split" content in a cell. Someone without much Excel experience has typed an entire mailing address in one cell, i.e. 123 Main Street, Ste. 100, City IL zip. Is there a way to split them? I've used the Data/Test to Columns feature. The problem is some of the cells contain commas, some don't, some contain more info than others, like P.O. Box as well as the street address, some have city, some don't. Any ideas?

Good morning,day,afternoon,evening everyone.

I have a list of client addresses about 12000 rows long, I want to see how many clients live on a certain street... Like, to get a counter listing how many live on Main St. I'm assuming this is a variation of the countif formula...? I just don't know how to read PART of a cell.


I'm working with a spreadsheet in Excel 2010 and am trying to extract all of the Street Numbers (all are 3 or 4 Digits) from an Address Column (actual addresses starting in Cell C2), and placing them into a New Column/Cell. However many new Columns I need to create (1 or 2) doesn't matter, I just want them separated:

Original Headers: A1: First Name, B1: Last Name, C1: Address, D1: City, E1: State, F1: Zip Code

Sample Address Data: 5645 Vista Lane

I apologize in advance if this is a simple task, or it was already discussed. I've searched the forum/internet for days, yet regardless what formula I try, I can't get it to work. I'm obviously doing something wrong.

Any help would be greatly appreciated!