I'm a GIS novice. I have a CSV file that contains the latitude and longitude data for a small region in the northeast U.S. The data appear in one column appearing as one uninterrupted string of text in each field. I need to separate this data into two columns.

Each field is formatted like this, with all fields in this column having the same number of digits:


Two other issues: each field begins with "xy," and the longitude (or "y") should have a minus sign in front of it because the location for this map is within the western hemisphere.

How can I separate this column into two columns so that 1) the "xy" disappears and 2) the longitude includes a minus sign? OR will QGIS3 be able to automatically read and correctly interpret this column because of placement of "xy" before each number?

I've searched answers to this "one-column to two-column" issue, but all the questions referred to lat-long figures separated by a comma in their respective fields. There were some references to use of the field calculator - but I don't know how to use that, either.

  • I strongly suggest you get familiar with the field calculator, since it is a basic tool of QGIS. You also should try to find out, which CRS these coordinates actually are supposed to be in.
    – Erik
    Feb 12 '21 at 15:02
  • As an alternative, you could do is similarly in a program like Exvel, before importing to QGIS
    – Babel
    Feb 12 '21 at 15:37

You will need to learn how to use field calculator. See this section of the manual to start.

Then, your field is a string (text) field from which you need to extract the relevant substrings for the x coordinate and y coordinate and convert them into numbers.

The expression substr(string,startpos,length) will let you extract length characters starting at startpos from string. You will then need to convert that to a number, using the to_real(...) function. For instance, if the attribute with the xy strings is called XYString, then the following would return the number represented by the 8 numbers after the y, so 42850210 for the example you gave.


Note you will have to figure out what coordinate system the numbers are in, and how many digits for x and y, and you may need to do further post processing e.g. if there is an implied decimal point.

As advanced operations, which I'm afraid may not make sense to you right now but may be useful to you or others later, you might first split the string into the x and y, then recombine into a geometry (point) in WKT format, and have QGIS interpret it that way. For instance, as something you'll need to modify to fit your purposes, something like:

'POINT(-' || substr("XYString",3,9) || ' ' || substr("XYString",12,7) || ')'

would repack your strange xy string as POINT(-428502107 0865890) which could then be interpreted by QGIS as a WKT geometry. But you'll need to figure out what the coordinate system (CRS) is and probably adjust significant digits. In that example, you'll see I snuck in a - to make the x coordinate negative as you requested.

  • The 7 digits 4285021 feel like a UTM Northing, and 708658 could be a UTM easting. You'd have to know the zone, though, and not sure what the other digits would be. As a stab in the dark, if it were zone 17N, then those coordinates point just outside Shenandoah National Park west of Washington DC. You can play with different zones and options at engineeringtoolbox.com/utm-latitude-longitude-d_1370.html
    – Houska
    Feb 12 '21 at 16:06

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.