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

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

Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a grea ...
Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...
Get the First Word from a Cell in Excel
How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or ...
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 ...

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


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

I have a list of email addresses in an Excel 2007 file. I need to copy and paste those addresses to send an email. The one email program I use, uses commas between each email address to send to more than one person.

The other email program places the email address below each other in individual BCC or TO lines.

I saved the file as .txt and copied. In the first email program using commas, it would only copy the first address.

In the second email program it would copy across and put in commas but all were red since that program needed each address to be on a separate line.

Hi Everyone
Hope some Wizard can help - I suspect it is (fairly) easy to solve - but I'm sadly not an Excel expert!

I have an address list of 64,000 entries that I want to geo-code (the geo-coding isn't the problem...). A large % of addresses don't have street numbers and this just gives a general position based on the Post/Zip code which isn't accurate enough - it needs to have an actual street number.
So, I want to extract all of the addresses without a street number so I can cross check them with something else (not an Excel problem). Trouble is some of the street numbers are embedded in a string - might be a multiple NOT problem
Attached a sample file...


Paul Pestille

Is there a formula that will remove the numbers from an address and leave the name? I need to keep the whole address but seperate the numbers from the street name. There are 3 to 5 numericals per address and > 15 letters if that makes a difference. The purpose is to sort by street name.

I have a column (Excel 2007) which contains 20,000 addresses. I need to extract the "street / road name" part of the address and place it in a separate column.
Each address consists of a number of parts - generally 5 to 7, and each part is separated from the next by a comma
Example 1 (5 parts)
12, Any Road, Nottingham City, Nottingham, NG5 7HY

Example 2 (6 Parts)
Appt 3, 45, Any Street, Nottingham City, Nottingham, NG8 2JY

Example 3 (7 Parts)
Flat 18, High House, 107, Any Drive, Nottingham City, Nottingham, NG1 9FT

Whilst the number of parts prior to that which I wish to extract varies there are always 3 parts after the part I wish to extract and those 3 parts are always 38 characters in total. I'm sure this must be possible using a combination of the Text functions but to date I havent found the right combination. Text to Columns won't work because of the differing number of parts preceeding the part I want. Does anyon have any ideas / suggestions? Thanks for you time!


I have recently changed my software at my work from Sim-pro to Clik. I have about 4000 customer details that have been exported from Sim-pro to a CSV file but unfortunately some of the addresses are in one cell and need to be split up into 2 or 3 cell so it can be imported into Clik. In the Sim-pro program addresses are entered into one dialogue box e.g Address 1(house name - if applicable), Address 2(street name) & Address 3(suburb/area), after each line you press enter to separate them. The town/city, county and postcode do have their own entry box though and these do import normally into excel.

Unfortunately to import the addresses from the CSV file into Clik the Address parts 1,2 & 3 need to be in their own cells but obviously these are all in one cell(not every address have 3 parts some just use 1 address line which is fine). Can excel recognise the imported information from Sim-pro that is separated by 'enter' in that one cell and move them into another cell? I have tried using text to columns and selecting delimited and entering 'ALT 010' in the 'other' box but that doesn't work.

I could really do with some help here if possible, I'm dreading the thought of going through 4000 addresses and cutting and pasting parts of the cells....



Hi all

I have two lists of house addresses, with each coming from a separate source so the data is not 100% comparable.

The first list has a route number for each address and the second one doesn't. I need to match addresses and then input the matching route number into the second sheet.

Sample data:

******** ******************** ************************************************************************> Microsoft Excel - Book2 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 Sheet 1 Sheet 2 2 Route no Address Address 3 36a 1 Grand Way "The Gallops" 1 Grand Way, Seapoint 4 36d 5 Coe Road 5 Coe Road, Central 5 42c 7 Main Street Central High School, 7 Main Street 6 21f 23 Long Road Barry's Newsagents, 23 Long Road 7 32g 28 Angel Street 28 Angel Street, The Hills Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

You will notice that the address fields do not match up 100%. I realise that I will probably need to do some of the work manually.

I need to add route numbers to the list in "Sheet 2" by looking up the values in "Sheet" 1.

I'm fairly proficient in Excel, but have no clue as to how to go about coding in VBA.

Any assistance would be much appreciated!



I've been given a list of 10'000 addresses, each in a single cell in its own row. I need to separate them into 4 rows: Unit Number, Street Number, Street name and Street type. This would be simple if address contained the same amount of spaces to separate them with however some addresses is a unit it contains an extra space to separate the unit number from the street number.

If I could use text to columns to separate pushing data to the left instead of the right this would be pretty easy but I don't believe its possible. I'm fairly certain I'll have to do a lot of the grunt work myself but any ideas would be great. I've tried setting the last column to filter out all but blanks and then pasting one row to the right but the data doesn't paste straight right due to hidden columns etc

Excel 2010 btw Example_photo.png


I am using Excel 2007 and have a spreadsheet with a single column (A)

The column contains addresses copied and pasted from a Word document
where the street address was on one line and the suburb and postcode
was on the next line so the Spreadsheet has the same sequence.

I need to have the street address, suburb and postcode in the same cell.

Is there a way to automate this?



I have a list of account identifiers and addresses. Based on the DB entries there are some duplicate addresses but mostly address with variations i.e. 1110 W post ST vs. 1110 West Post Street, and the variations can also include a street address with a PO box and without a PO box but they both have the same street.

Is there a way to do an IF statement or something similar that looks for duplicate account identifiers and then "similar" address info rather then complete or exact matches?

I need to come up with those account identifiers that are duplicate but have completely unique address information.


Account ID | Address
1234 | 1110 W Post St
1234 | 55 Harbor way

I need to eliminate

Account ID | Address
1234 | 1110 W Post St
1234 | 1110 West Post Street P.O. Box 224

Hi all

I am trying to simplify the manipulation of data to create a full postal address from a range of cells.

Due to the varied way in which postal addresses are made up, I have found it necessary to create a 2nd Worksheet, which uses helper columns to ensure that spaces and commas appear in the right places.

I recently came across some code on this site from JBeaucaire which I have tried out but in my particular workbook, the commas and spaces do not come out correctly.

The enclosed sample workbook contains a selection of addresses which should cover most of the variations for addresses in the way that they are entered in my main workbook.

Sheet1 (Master) is the main data.
Sheet2 shows the formulas that I have used to create a full postal address.
Sheet3 makes use of a VBA Module and shows the results.

I felt that VBA code is the right way to go but I'm unable to modify the code to make it work or come up with an alternative VBA solution.

TIA ...spellbound

I'm really new at excel and the search results for what I need were greatly over my head. All I need to do is change:

(cell)1234 Any Street or Ave


(cell)1234 (cell)Any Street or Ave

The street number can be any length from 3 digits to 8 digits. Can this be done? I have over 2000 addresses to change.

Many thanks.

How would I copy a list of address labels into excel and then get the names,
street addresses and city/state/zip into three seprate columns so as to be
able to sort, etc. without copying the individual label info one at a time (I
have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into
A2, City into A3. I want A, B, C without having to do them one at a time.
Any suggestions? I'm sorry if this question is too poorly written to

In Word, when you find and replace a value, it is replaced in the case of the original text, but Excel replaces the values with the case you've typed in the "replace with" box.

e.g. correcting errors in spelling of addresses replacing "stret" with "street"
HIGH STRET (Original Text)
HIGH STREET (Word after Find & Replace)
HIGH street (Excel after Find & Replace)

Does anyone know a way of making case of the "replaced" text match the case of the original (as it does in Word)?
Unfortunately, I can't convert everything to proper case as the addresses could contain company names which are initials which need to remain as capitals (e.g. IBM).