I have list of cities and I want to create URL for each of them in Google Sheet. Scenario is most of the part of the URL is same only at the end city name is different so I created a formula

=ARRAYFORMULA(CONCATENATE("abc.org/area/city-",LOWER(SUBSTITUTE(E14," ", "-",1))))

  • Here E14 is the column value where I put the city name.
  • But this formula is not working for multiple words city name. For example:

abc.org/area/city-world-trade center

  • I want to apply for whole column E and generate automatic URLs for each city name.

How can I apply this formula for multiple words city name in GOOGLE SHEET.

1 Answer 1


Try getting rid of the ,1. That instructs SUBSTITUTE to only substitute the first instance it finds, which you don't want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.