Skip to main content

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

Forum|alt.badge.img+11

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!
This topic has been closed for replies.

2 replies

Kenneth_Raghuna
Forum|alt.badge.img+20

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]+,"), ",", "" ) )
    Forum|alt.badge.img+11
    • Lisa_H
    • Author
    • Known Participant
    • February 6, 2025

    Thank you!  I will give it a try!

    Terms & ConditionsAccessibility statement