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
Here’s how I was able to get the parts of a Lat Long:
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)
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!
Thank you for sharing those formulas and explanation!