Excel Formula to get text between last two commas

Question

I have this data in cell A1:

Majestic Properties Design District, LLC, Ste. 101, 35 NE 40TH ST, Miami, FL 33137

There are a few other cells with these format address too, e.g.

Fortune International Realty Iriondo Ecker & Assoc. Inc., Ste 25, 260 Crandon Blvd, Key Biscayne, FL 33149

The output I want is

Miami
Key Biscayne

and so on

To get the Zip and State I did

=RIGHT(A1,8)

However now I need to get the city (that is the text between two commas from the last).

I do not want to use TEXT to COLUMN or any VBA. I would like to use a formula.

Not a programming question. Should be asked at Super User instead — brettdj, Jun 20 '12 at 10:39

score 8 · Answer 1 · answered Jun 13 '12 at 09:45

8

This formula should also work, doesn't rely on the length of the zip code....

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",100)),200),100))

answered Jun 13 '12 at 09:45

barry houdini

45,615
8
63
81

+1: Nice trick. You are relying on LEN([Zip]&[State])<=100 but I guess that's fair enough :) – lori_m Jun 13 '12 at 11:34
Amended condition: LEN([State Zip]&[Town/City])<=100 – lori_m Jun 13 '12 at 12:02
True enough.....you can always use LEN(A1) and LEN(A1)*2 in place of 100 and 200..... – barry houdini Jun 13 '12 at 12:54

score 4 · Accepted Answer · answered Jun 13 '12 at 06:57

4

Maybe this:

=REPLACE(LEFT(A1,LEN(A1)-10),1,FIND("|",SUBSTITUTE(A1,",","|",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))-1))+1,"")

answered Jun 13 '12 at 06:57

lori_m

5,487
1
18
29

+1 Nice, but this assumes that the length of the string from the last comma to the end is *always* equal to 10. If it isn't (e.g. if even a space delimiter is missing `,FL 33149`), then this formula will fail. – Jean-François Corbett Jun 13 '12 at 07:08
@Jean-FrançoisCorbett Yes, I'm assuming the string ends with comma followed by a space and then an 8 digit zip code. If there is variation he would need to follow your suggestion. – lori_m Jun 13 '12 at 07:12

score 2 · Answer 3 · edited May 23 '17 at 10:32

You ask how to extract the text sandwiched between the first and second rightmost commas in a cell.

Counting instances of a character starting from the left of a cell is easy. However, counting starting from the right is notoriously difficult in Excel. The higher you count, the more difficult: In general, the length of the worksheet formula you need to type if you want to find the nth instance of a character grows exponentially as something like 4^n! (If you want to do this in one single formula.)

You may want to have a look at these earlier answers of mine to similar questions.

These answers show how to determine the position of the first instance of a character from the right (and then extract the entire text to the left or right of that). You need to locate not only the first but also the second instance of , (n=2) so your formula will be 16 times longer than the formula in those answers. Again, this is if you want to do it in one single formula.

If having an extra cell containing intermediate results is acceptable, then you can do this:

In cell A6: your address text

In cell A7, we remove the first comma from the right and all the text after that:

=LEFT(A6,FIND(CHAR(1),
 SUBSTITUTE(A6,$A$3,CHAR(1),LEN(A6)-LEN(SUBSTITUTE(A6,$A$3,""))))-1)

In cell A8, we keep only the text after the next rightmost comma:

=TRIM(MID(A7,FIND(CHAR(1),
 SUBSTITUTE(A7,$A$3,CHAR(1),LEN(A7)-LEN(SUBSTITUTE(A7,$A$3,""))))+1,LEN(A7)))

Note that I store the , delimiter in cell A3, so it's easy to change.

Done. The result looks like this:

enter image description here

If you must have only 1 formula and no cell with intermediate results, then in the fomula of cell A8, replace every instance of A7 with LEFT(A6,FIND(CHAR(1), SUBSTITUTE(A6,$A$3,CHAR(1),LEN(A6)-LEN(SUBSTITUTE(A6,$A$3,""))))-1). I forget what the limit is on the length of formulas in Excel...

Excel Formula to get text between last two commas

3 Answers3