Google-sheets – Find whitespace and replace with string from formula in Google Sheet

formulasgoogle sheets

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))))

abc.org/area/city-new-york 
abc.org/area/city-fort-wayne
  • 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.

Best Answer

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

Related Topic