Reputation: 21
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
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
Reputation: 421
Try the following formula.
=MID(A2,FIND("^",SUBSTITUTE(A2,",","^",1))+1,FIND("^",SUBSTITUTE(A2,",","^",2))-FIND("^",SUBSTITUTE(A2,",","^",1))-1)
Upvotes: 1
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