Formatting Dotted Decimal Values

[sahaider]

Hello Experts,

I have single column showing decimal latitude and longitude of some points and they are separated by commas. This column has multiple no of rows but the no of digits after the decimal for each entry is not static. I would like to have code that will read the latitude and longitude separately for upto 5 decimal points and place them in two separate columns for each row entry.

So i have:
[TABLE="width: 500"]
<tbody>[TR]
[TD]54.9208946686,-1.74049906
[/TD]
[/TR]
[TR]
[TD]54.919392,-174418236
[/TD]
[/TR]
[TR]
[TD]54.89470,-1.5778952
[/TD]
[/TR]
[TR]
[TD]54.9547455,-1.737341
[/TD]
[/TR]
</tbody>[/TABLE]

May thanks in advance.

Regards
Syed

 

[tyija1995]

Hi Syed,

Given that column A holds the latitudes and longitudes, starting from cell A1 -- use these formulae in B1 & C1 respectively:

B1:
=VALUE(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,SEARCH(".",$A1)+5))

C1:
=VALUE(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(2-1)*LEN($A1)+1,SEARCH("|",SUBSTITUTE($A1,".","|",2))+4)))

 

[sahaider]

Hi Syed,

Given that column A holds the latitudes and longitudes, starting from cell A1 -- use these formulae in B1 & C1 respectively:

B1:
=VALUE(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(COLUMN()-2)*LEN($A1)+1,SEARCH(".",$A1)+5))

C1:
=VALUE(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),(2-1)*LEN($A1)+1,SEARCH("|",SUBSTITUTE($A1,".","|",2))+4)))

Hello Tyija1995,

Thanks. it works.

Regards
Syed

 

[FormR]

Hi, here's another option you can also try:

Excel 2013/2016
	A	B	C
1	54.9208946686,-1.74049906	54.92089	-1.74049
2	54.919392,-1.74418236	54.91939	-1.74418
3	54.89470,-1.5778952	54.8947	-1.57789
4	54.9547455,-1.737341	54.95474	-1.73734
Sheet1

Cell Formulas
Range	Formula
B1	=TRUNC(LEFT(A1,FIND(",",A1)-1),5)
C1	=TRUNC(MID(A1,FIND(",",A1)+1,99),5)

 

[ClaireS]

And another version!
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C
1		Processed
2	Original	Lat	Long
3	54.9208946686,-1.74049906	54.92089	-1.74050
4	54.919392,-1.74418236	54.91939	-1.74418
5	54.8947,-1.5778952	54.89470	-1.57790
6	54.9547455,-1.737341	54.95475	-1.73734
Sheet1

Cell Formulas
Range	Formula
B3	=ROUND(VALUE(LEFT(A3,FIND(",",A3)-1)),5)
C3	=ROUND(VALUE(RIGHT(A3,LEN(A3)-FIND(",",A3))),5)

[/FONT]