Split out latitude and longitude to create map visual

I’m trying to map out NASA facilities by latitude and longitude, but first I need to transform the data so that latitude and longitude are separate columns, and as numbers. I was trying a series of split actions, but I’m having difficulty removing the last parenthesis. The preview shows it removed, but when I click “Confirm” it appears in the table with the parenthesis, which prevents me from transforming it into a number.

What are suggested next steps?

Here’s the original dataset: https://data.nasa.gov/Management-Operations/NASA-Facilities/gvk9-iz74

1 Like

Here’s how I was able to get the parts of a Lat Long:

I used SplitPart to cut the Lat Long into two pieces, splitting on the “,” character.
I then used SubString to cut off the leading and trailing “)” and space.
And then I converted the column into a number.

In the end, I have:
Column [Second Half]: SplitPart([Lat Long], ",", 2)
Column [Remove Paren]: Substring([Second Half], 2, Len([Second Half]) - 2)
Column [Long]: Number([Remove Paren])

Or you could combine them altogether:
Number(Substring(SplitPart([Lat Long], ",", 2), 2, Len(SplitPart([Lat Long], ",", 2)) - 2))

If you are using Substring to remove the leading space and trailing “)”, your problem may be arising from the length you are using. You should be sure to use Len - 2, to account for the space being removed from the front in addition to the “)”

Hope this helps!

1 Like

Thank you for sharing those formulas and explanation!