add comma to address

July 24, 2009 at 03:59:48
Specs: Windows XP

Hi, I ultimately need to take a list of addresses and separate them into one cell/column for street and and a second for city, state and zip. The street must be in its own cell, separate from the other three but it doesn't matter if the three are in their own cells. There is no comma after the street address in the list so I can't use the "text to column" command in excel. I either need a formula, macro etc that will add the comma after the street and or one that will just separate the street and city/state/zip into two columns. Is there a way to do this without going into each line manually? Thanks for the help.

See More: add comma to address

Report •

July 24, 2009 at 08:20:36

I can't use the "text to column" command in excel

Sure you can, just use the Fixed Width option,
Click Next
You should see several lines like arrows seperating each word, just left click and hold, on a line and move it to where you want it.
You can combine several lines to create large or small sections.


Report •

July 24, 2009 at 11:29:17

I just reread your post.

There is no comma after the street address in the list so I can't use the "text to column" command in excel

Have you tried using a "space" as the seperator?

The Text to Column wizard under Delimited allows several different options for delimiters. Tab, Semicolons, Space along with a Comma and Other.

Using the space should get you very close to what you need.


Report •

October 7, 2009 at 13:43:23

I have a SIMILAR QUESTION, but it's more complicated (maybe).
I keep running into problems with the "text to column" converter, when it comes to converting names to columns.
Many lists include people with 3 names, or names with initials. It screws the whole thing up, it seems.
You can't delimit with a space, it seems. Because your second column will end up including both Last names and Middle names, with the occasional 3rd row of last names for those few who have 3 names.
How do I get around that???

Report •

Related Solutions

October 7, 2009 at 14:56:57


There is a small set of custom text functions which allow you to split text based on different delimiters, and the delimiters can be counted from the beginning or the end of the text. As delimiters can be any text, you can extract a word and then use it as a delimiter in the next function.

For example in cell B2 is the name Alexander G. Bell
in C2 is =xtndleft(B2," ",1)
in E2 is =xtndright(B2," ",-1)
sandwiched in the middle in cell D2 is =xtndmid(B2,C2,1,E2,1)
which uses the extracted first and last names to get anything inbetween

Here is the output for three names, one with a middle initial, one without and one with a full middle name.
The formulas on row 2 were just dragged down. the same formulas worked for all three name variations.

	B			C		D		E
2	Alexander G. Bell	Alexander	 G. 		Bell
3	Alexander Bell		Alexander	 		Bell
4	Alexander Graham Bell	Alexander	 Graham 	Bell

These text functions are UDF's (user defined functions) and have to be entered into a visual basic module, but it may be worth the effort, rather than doing the separation manually.

These formulas should work for the address issue, especially if city, state and zip are present for all addresses.

For example:
1776 W State St Boise Idaho 64919 in cell B5 can be split into:

	C			D	E	F
5	1776 W State St 	Boise	Idaho	64919
with the following formulas
C5	=xtndleft(B5,D5,1)
D5	=xtndmid(B5," ",-3," ",-2)
E5	=xtndmid(B5," ",-2," ",-1)
F5	=xtndright(B5," ",-1)

The formulas work by getting text starting at the end using the spaces and once 'Boise' was found, the street address was just everything to the left of 'Boise'

Once you have entered the four formulas just drag them down as usual for further addresses

These functions are free and can be found here.


Report •

October 7, 2009 at 17:54:24

Here are three formulas for getting the Last, First and Middle names:

Cell A1 contains the name.

To get the First Name:

=LEFT(A1,FIND(" ",A1,1))

To get the Last Name:

=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

To get the Middle Initial or Name:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))


Report •

Ask Question