Creating a calculated field - Looker Studio

[naeljunior]

Good morning! I have an address field, which contains the street name, number, city and state. I want to create a calculated field to get just the city name. I'm new to using the Looker Studio tool, so I'm not sure how to get this information. Below I will show an example:

"Av. Borges de Medeiros, 3120 - Praia de Belas, Porto Alegre - RS, 90110-150"

“Porto Alegre” is the name of the city at that address. How do I achieve this?

[varunikaraja]

Thank you for your query!

To be able to parse a string like this - the string must be formatted consistently. If the format isn't consistent then you have no way of knowing what element or position in the string to extract. Ex.: Is zip code always going to be in a range such as listed in your example of "xxxxx-xxx" or can it sometimes only be a 5 digit "xxxxx" 

For a string with this format nnnn, street name, city name, area you can parse out the 3rd element of the string. For a string with this format nnnn city, country, area you can parse the second space element of the first comma element.

Before you can even begin to code - you need to identify all of the valid formats and figure out how to identify that format. Once you have identified all of the valid formats - and a method of determine each format, then you can use one of several different methods to parse. 

• Example: Zip: =RIGHT(address_string,5)  --> count from the right end of the string for 5 places and treat it as the zip 
• Street: =LEFT(address_string,FIND(",",address_string)-1)
  City: =TRIM(MID(SUBSTITUTE(address_string,",",REPT(" ",255)),255,255))
  State: =MID(A2,LEN(A2)-7,2)