RegEx Parsing Address with Different Structure

[mcarvalho]

Hi,

I want to parse the following Address column to get the City - State for each location. The pattern that I need is ", City | State (2 digits),"

For example, in the data below the results that I want are:

1) Vitoria | ES

2) São Gonçalo | RJ

3) Salvador | BA

Address

Av. Saturnino de Brito, 80 - Praia do Canto, Vitória - ES, 29050-385, Brazil

Praca Calos Gianelli s/n - Lja 409 - Alcantara, São Gonçalo - RJ, 27930-560, Brazil

R. das Pedrinhas, 58-70 - Plataforma, Salvador - BA, 40717-630, Brazil

Estr. do Lageado Velho, 1501 - Guaianases, São Paulo - SP, 08451-000, Brazil

Av. Ipiranga, 6681 - Partenon, Porto Alegre - RS, 90619-900, Brazil

16 - Smdb Conjunto 12, 9 - Lot 02 - Lago Sul, Brasília - DF, 71680-120, Brazil

Av. de Santa Cruz, 11225 - Sen. Vasconcelos, Rio de Janeiro - RJ, 21710-231, Brazil

Av. Visconde do Rio Branco, 360 - Loja 124 - Centro, Niterói - RJ, 24020-000, Brazil

Av. Gov. Roberto Santos, 116-122 - Centro, Santo Antônio de Jesus - BA, 44572-060, Brazil

Av. Brasil Norte, 289 - Cidade Jardim, Anápolis - GO, 75053-520, Brazil

BR-070, Shop 36 - Mansões Centroeste, Águas Lindas de Goiás - GO, 72915-700, Brazil

R. Cel. Carvalho, 275 - loja 4 - Centro, Angra dos Reis - RJ, 23900-315, Brazil

R. Dr. Manoel Patti, 444 - Centro, Passos - MG, 37900-040, Brazil

How can I do it using RegEx?

[apathetichell · Accepted solution]

I used two regex_replace tools in a formula to get this but there are probably a slew of ways...

parse mode - make sure that case insensitive is not checked.

.*,\s(.*)\s-\s(\u{2}).*

[mcarvalho]

That worked perfectly!