I have thousand list coordinates from excel format (CSV file), its projection is Geographic WGS 84 (EPSG 4326) most of all. All of the coordinates are inserted manually from our surveyor teams. But we know that sometimes we make mistakes by putting wrong format coordinates, for example the correct coordinates is "6.504302, 112.786543" but they insert it as "6504302, -112786543" because of human errors. It's quite tiring to find the mistakes among thousands list excel cell and sheet, it also takes times too.

Are there apps, tools, or methods to find and replace the mistakes with correct values?

  • You could sort in Excel largest to smallest then rectify the ones that come to the top. Jul 12 '18 at 4:45

Select the coordinates that does not have a dot . and save them as CSV file. If all the digits in latitude coordinates should add a dot after first digit, and add a dot after 3 digits in longitude coordinates, Then in Notepad++ load the CSV file and use Find and Replace with the following regex:

Find: (^\d{1})|(-\d{3})

Replace All: $1$2.


before formatting:

6504302, -112786543
8998070, -113998889
1234567, -118976541
0987654, -119292929
9087653, -111979797
6757482, -115098087

After formatting using find and replace all:

6.504302, -112.786543
8.998070, -113.998889
1.234567, -118.976541
0.987654, -119.292929
9.087653, -111.979797
6.757482, -115.098087

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.