Help

How do I use a formula to pull just the City out of a lookup cell?

Topic Labels: Formulas
184 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Lisa_H
6 - Interface Innovator
6 - Interface Innovator

Hi,

I have one column in my Jobs Table that pulls the Job address from my Table of Customers, its a Formula and seems to work fine it puts the correct Job address into the Jobs Table if the customer has a different Mailing address, it looks like this:

 

IF({(J) Address?} ="Yes", {Mailing address (from Customers)}, {(J) Street} & " " & {(J) City,St,Zip})

I have a second column in the Jobs Table that I need to JUST have the City Name appear in.  I have tried to write the formula, and it just returns an Error Message, it looks like this:

 

IF({(J) Address?}="Yes", LEFT({(M) City, St. Zip (from Customer)}, SEARCH(",", {(M) City, St. Zip (from Customer)})), LEFT({(J) City,St,Zip}, SEARCH(",", {(J) City,St,Zip})))
 
Is there a better way to pull the City out of the Job Address Column?
I feel like I'm beating my head against a wall.
Thank you!
2 Replies 2
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

Assuming that there will only be one comma in the job address column as shown -- right before the city -- this should work:

IF({Job Address},
  SUBSTITUTE(
    REGEX_EXTRACT({Job Address}, "[^\\s]+,"),
  ",",
  ""
  )
) 
Lisa_H
6 - Interface Innovator
6 - Interface Innovator

Thank you!  I will give it a try!