Converting into gps coordinates

[SophieLR]

Dear forum.

I have made an Excel sheet, where I entered (copy-pasted) gps coordinates from Google Maps. The column settings in Excel were not adjusted from the beginning, because I couldn't seem to find the right setting. This means that the current format is e.g. 55.419.474 10.455.039 and should be 55.419474 10.455039. How do I change this in a smart way? Because I have several hundred gps points I have to correct now. I will really appreciate your advice.

Best wishes, Sophie

 

[steve the fish]

Hi. Presuming thats in one cell then try this:

=SUBSTITUTE(SUBSTITUTE(A1,".","",4),".","",2)

 

[SophieLR]

Dear Steve.

Thank you! I'm sorry I forgot to mention that the numbers are in two cells, like this

55.009.997

9.253.622

<tbody>
</tbody><colgroup><col><col></colgroup>

It is possible to "drag" the code and thereby apply it to all my Excel cells, even though the numbers are not the same (the format is however the same)?

/Sophie

 

[steve the fish]

Sure. The formula i provided would work but you can just use this. Drag it across and down.

=SUBSTITUTE(A1,".","",2)