+18I have a field with city names and state names in this format:
Oklahoma City OK
Oakland CA
Dallas TX
Forth Worth TX
Augusta ME
I want to do the following:
Extract the City name into one field and the state abbreviation into another field.
The only consistent pattern in the data is the state abbreviation always comes after the last space in the string.
I think I need to create a RegEx expression to do be able to do this.
Can someone help me with how do this?
Best answer by DanAtSafe
Hi
+22Hi
+24Edit: I got my statements wrong and can’t seem to update this post...
You can also do what
City Name: @Substring(@Value(Original Data),0,-2)
State: @Substring(@Value(Original Data),-2,2)
+24I don’t know if this is the most efficient way to do this but it works if the data will always be structured as you say <city Name> <space> <two letter state abbreviation>.
In an attribute manager you can create two new attributes and input the following in the text editor for the values:
For City Name: “@Left(@Value(<your original column>),(@StringLength(@Value(<your original column>))-3))”
For State: “@Substring(@Value(<your original column>),-2,2)”
Someone else may have a cleaner way of getting the City Name. I tried using @Substring(@Value(<your original column>),0,-3) but it did not work.
+52Hi
Good Job
+52
Yep
