Split latitude and longitude AND limit to 6 decimal places

[PatRichard]

I need to take a returned latitude and longitude value and split it into two cells. And for each, I need to limit the values to 6 digit places.

For example, I get back a value of "42.3006892,-83146552" and put it into Q6. I need it to have one cell (latitude) with 42.300689 and one (longitude) with -83.314655.

For the latitude, I can use

LEFT(Q6, FIND(",", Q6) - 2)

and get what I need. For the longitude, the closest I've figured out is

RIGHT(Q6, LEN(Q6) - FIND(",", Q6))

. I just haven't figured out the best way of getting the longitude to six decimal places. My bigger question is whether I'm headed in the right direction, or if there is a more elegant approach? Longitude can be >= 100, so I can't simply just use LEFT().

Thoughts?

 

[Cubist]

Try:

Book1
	A	B	C
1	42.3006892,-83146552	42.300689	-83146552.000000
Sheet2

Cell Formulas
Range	Formula
B1:C1	B1	=--TEXTSPLIT(A1,",")
Dynamic array formulas.

You can adjust the decimal places on the Home tab.

 

[RobP]

Hi,

maybe you can use :

Book1
	C	D	E	F	G	H	I
1
2		42.3006892,-83.146552				42.300689
3						-83.14
4
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LEFT(D2, FIND(",", D2) - 2)
H3	H3	=ROUNDDOWN(VALUE(RIGHT(D2, LEN(D2) - FIND(",", D2))),2)

sorry, the 2 at the end reounds down to 2 figures - change to 6

 

[felixstraube]

Maybe this?:

(I'm assuming that the data in cell Q6 actually should be "42.3006892,-83.146552", with the second period).

Book1
	Q	R	S
6	42.3006892,-83.146552	42.300689	-83.146552
Sheet3

Cell Formulas
Range	Formula
R6:S6	R6	=ROUND(TEXTSPLIT(Q6, ","),6)
Dynamic array formulas.

 

[PatRichard]

Try:

Book1
	A	B	C
1	42.3006892,-83146552	42.300689	-83146552.000000
Sheet2

Cell Formulas
Range	Formula
B1:C1	B1	=--TEXTSPLIT(A1,",")
Dynamic array formulas.

You can adjust the decimal places on the Home tab.
View attachment 114551

So I'm using a VBA method (method #2 from How to Use Geocoding in Excel? - 2 Examples - ExcelDemy) for the geocoding, and the buttons above seem to have no effect. I tried formatting the cells as well. No joy.

Hi,

maybe you can use :

Book1
	C	D	E	F	G	H	I
1
2		42.3006892,-83.146552				42.300689
3						-83.14
4
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LEFT(D2, FIND(",", D2) - 2)
H3	H3	=ROUNDDOWN(VALUE(RIGHT(D2, LEN(D2) - FIND(",", D2))),2)

sorry, the 2 at the end reounds down to 2 figures - change to 6

Yes - this seems to work. I had looked at round(), but anything I tried resulted in the last number being changed, which results in an incorrect coordinate. This data is being fed into a phone system for 911 calls, so it's important that it be as close as possible.

Thanks!

 

[RobP]

great thanks for the feedback.

Rgds
Rob

 

[Cubist]

and the buttons above seem to have no effect. I tried formatting the cells as well. No joy.

I'm not familiar with the gecoding, but try select the cells -> Ctrl + 1 -> Numbers -> Decimal places =6.