Change order of data in a column.

[GAPP]

Hello everyone,

I have a problem, that I would like to have fixed. I am trying to bulk upload some geo locations for my site, but the data I have is longtude first and latitude after, whereas the site accepts the data reverse, which means latitude, longitude.

It's absolutely annoying, but I was hoping, that it's possible to change the order somehow.

Here is a example of how it looks:

"(12.595342677449292 55.66937339270993, 12.595097670457731 55.669239938648126)"

Any help is greatly appriciated.

[popipipo]

lat: =MID($A1;FIND(",";$A1;1)+2;37)
lon: =MID($A1;2;FIND(",";$A1;1)-2)
=lat &", "&lon

[GAPP]

lat: =MID($A1;FIND(",";$A1;1)+2;37)
lon: =MID($A1;2;FIND(",";$A1;1)-2)
=lat &", "&lon

Thanks alot! Now to my next question, how do I implemt that?

[GAPP]

betalingszone (1).csv

I uploaded the file, so you could show me perhaps.

[popipipo]

Take a look at my file in #2
Through a CTLR F3 you get to the 'Namemanager'

[GAPP]

Ok, found out how to add the exceptions in the Namemanager, even though I'm not sure if it's entirely the same, as my is in danish. Do I need to point the exceptions to what cells it needs to look at?

I would really appriciate it, if you would take a look at the excel sheet, so I know exactly what to do next time.

[GAPP]

I just realized, that there is no change in the example you provided. It's the same coordinates in all of the cells. I want the coordinates to start with 55.xxxxxxx and then 12.xxxxxxx

[newdoverman]

Create a new worksheet and enter this in B2 of the new worksheet and copy down and across. This will reverse all the co-ordinates in columns B onward.

Please Login or Register  to view this content.

If column A is text that hasn't been converted to columns, use the text to columns feature so that the co-ordinates start in column B and use the following on a new worksheet.

Column A would then have this formula copied down the column:

Please Login or Register  to view this content.

[GAPP]

Hello and thanks for your feedback.

Okay, so I did create a new worksheet, and pasted the code into field B2 of the new worksheet. After that I copied it to B3, B4 all all the way to B10.

After that, I did the exact same thing for Column A, with the second formula.

Unfortunately there hasn't been any changes to reversing the coordinates. Do I still need to have the previous formulas in the "Namemanager" too?

Again, I appriciate all the help you are giving me right now.

[newdoverman]

Column A of your data needs to be processed with text to columns BEFORE creating the second worksheet. (I should have said that) the enclosed file should show what I was doing.

[GAPP]

Thanks alot.

Is it possible to keep all coordinates in one column, and one cell for each of the coordinate groups? The bulk uploader can only have so many columns, so for this to work on bigger databases, there is alot of work putting them in correctly.

Also, it seems that there is a problem showing the last three column cells, if you copy them to another sheet.

I'm trying to import all of these coordinates to a specialized Google map, which is found here: https://mapsengine.google.com/map/?

It seems it's having a hard time reading the coordinates.

[newdoverman]

To do what you want, you need a VBA solution. Formulae can't "push" values to cells, they can only calculate values or bring in values from other cells.

I would try and find out exactly what Google Maps requires before proceeding with the processing of your raw data. It could save doing a lot of work for nothing.

[GAPP]

I see. Not entirely sure what a VBA solution is, but maybe it's not neccesary afterall, since it's possible to import values from alot of cells.

Google Maps seems to be pretty fluid regarding requirements afterall, I guess it was more or less only because I wasn't filling out the fields correctly. For example, the text/number issue.

Here are the guidelines, that I c/p directly:

Some data sets might have trouble importing into Maps Engine Lite and Pro. There could be a few reasons for this failure, so make sure your spreadsheet meets the following requirements:

• It is a CSV, TSV, or XLSX file, a My Map, or a spreadsheet from Google Drive.
• It contains location information in one or more columns - this can be lat-long information, addresses, or place names. The more specific, the more likely it will map accurately.
• It is 100 rows or less. If your file is longer than 100 rows, split up your data into multiple files then import into separate layers. Rows beyond the 100 limit won’t be imported and won’t show up in the map or data view.
• It is formatted cleanly, with a first row that contains the column title(s).

[newdoverman]

It sounds as if you don't have to do anything to your data other than make sure that you don't have more than 100 rows. It might be also that the data should only be a few columns that require column headers.