Gabriel A
Gabriel A

Reputation: 21

How do I extract text between two commas in Excel?

How do I extract text between two commas in Excel?

92 4th Street North, Providence, RI 02904

In this case, how would I extract "Providence" substring using simple Excel formulas (LEN, FIND, LEFT, RIGHT, etc)?

Upvotes: 0

Views: 14197

Answers (3)

Nathan Swartzendruber
Nathan Swartzendruber

Reputation: 103

@RAJA-THEVAR's formula worked very well for me with a list of over 2500 addresses, as long as the address only contained two commas. With an address like "100 Washington Street, Suite 225, Denver, CO 80220" it returns "Suite 225." I used the following formula to identify and addresses that contained more than two commas:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Many of these many-comma addresses had strange formats or information, and I found it better to fix them by hand.

Upvotes: 0

RAJA THEVAR
RAJA THEVAR

Reputation: 421

Try the following formula.

=MID(A2,FIND("^",SUBSTITUTE(A2,",","^",1))+1,FIND("^",SUBSTITUTE(A2,",","^",2))-FIND("^",SUBSTITUTE(A2,",","^",1))-1)

Upvotes: 1

El Gucs
El Gucs

Reputation: 967

Try the following formula

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

Considering your data is on A1

Upvotes: 0

Related Questions