Convert data to geocoordinates with a function

[JofFrey]

Hello, I have a strange thing at work. Each time we open a file with geocoordinates Excel transforms them to numbers. As a result the data loses its coma and becomes a number, however Excel shows points in these numbers.
Furthermore when I want to import this into another program it copies the points so I have to change the data each time in Excel with concatenate(left(;",";mid(;) (see last line).
Is there a way in VBA to create a function "lat" and "lon" which transforms the cell so I no longer have to adapt the formula depending on the column where the data stands.
Thank you in advance.

PS I've tried anything in the Options section without any result.

lat	lon
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52.378.405	4.814.793.333.333.330
52,378405	4,814793333

 

[Rijnsent]

What format is your original file in? A CSV? Something else? If it's e.g. a CSV you can use the import settings to adjust for the decimal symbol, so the numbers get imported correctly. For that, you shouldn't open the file from Windows Explorer, but use the Excel import functionality.
Second: What is the output you're looking for? E.g. you last line, should that be "52.378405,4.814793333" ?

 

[JofFrey]

The original file is .csv but using the Excel import function does not solve the problem, using the formula underneath is even faster and quite frankly, not a lot of colleagues will be able to pull it of using the Excel import function.

The output I am looking for is the last line. I'll show you the formula I use on the first and second column:
=Concatenate(left(A1:2);",";mid(A1;3;20))
=Concatenate(left(B1;1);",";mid(B1;2;20))
I use 20 in the mid function because that is the maximum length of the data in the cell.
So what I would like to know if it is possible to create a function "lat" that does applies the first concatenate formula and a function "len" which applies the second.

 

[Rijnsent]

To be honoust, I wouldn't go that way. I'd say it's even more confusing for users to have an extra function named LAT and LON, especially as they will have to switch macros on. If you want to use macros you might as well build something that gives your user a button to select a CSV file, process it and spit out the desired output format that you could upload/push into the system you want to have it in.

This is a tutorial to create a UDF (user defined function) with VBA: How to create and use User Defined Functions in Excel You can use the macro recorder to get the basics of your formula in place.

Without macros, you can try a named LAMBDA: Excel LAMBDA: Creating Named and Unnamed Custom Functions

P.S. As a geographer, my eyes hurt when you're trying to make a number to have 2 digits before the decimal place for LON and only 1 for LAT... That works if you know all your coordinates have that property, but there is plenty of coordinates on our globe that don't work. That's why I would use another solution where the decimal symbol won't get destroyed on importing. Trust me, that is possible / that does work, I do it on a regular basis...