REGEX Lat/Long Formatting Help

[ijurgensen]

Gooooood Morning!

I have a series of lat/longs formatted as the following, each unique location is a single cell in the same column: 39.377468N 77.407287W

I need to get these formatted to two separate columns as: 39.377468 and -77.407287 (ie removing the "N" and "W", splitting these into two columns, and adding a "-" to the longitude value)

Is REGEX the right tool for this? I've never used it, and looking up the syntax was a bit overwhelming.

Thank you!

[MarqueeCrew · Accepted solution]

@ijurgensen ,

Here is help by explanation:
I see a space as a delimiter.  Using the Text to Columns tool, you should be able to convert the single field into 2 fields.  ReplaceChar([Field],"NSEW")  will remove the offending letters.  But those letters represent Positive (N, E) and Negative (S, W) values.  Before you delete the letters, you should add a "-" as a prefix to the value when it is S or W.

Those are my thoughtful tips.

Cheers,

Mark

[Kenda · Accepted solution]

Hi @ijurgensen 

I actually like to use the Formula tool when I am doing RegEx. You can create two new fields, one for Lat and one for Long. Then try using the Regex_Replace() function. For the pattern, use parenthesis to group the parts of your string and identify those pieces you want to keep. 

Here is a good website that will help you to practice RegEx: https://regexr.com/ 

[ijurgensen]

Thank you both! I'm working through solutions now. Much appreciated!

[Qiu · Accepted solution]

@ijurgensen 

here is my "slow" version comparing to the one from @MarqueeCrew 
The ReplaceChar is brilliant!