Skip to main content

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

L
  • Lisa_H
  • Known Participant
  • 19 replies

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!
1 Attachments

2 replies

Kenneth_Raghuna
Forum|alt.badge.img+2

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]+,"),
",",
""
)
)
TheTimeSavingCo
L
2 people like this
  • TheTimeSavingCo
    TheTimeSavingCo
  • L
    Lisa_H
L
  • Lisa_H
  • Author
  • Known Participant
  • 19 replies
  • February 6, 2025

Thank you!  I will give it a try!

Reply


Most helpful members this week

Terms & Conditions