Add decimals and rounding

[ExcelMK]

I have a data file of X/Y coordinates that I need to edit.

The data needs to end up in the following format:

X10345Y06523
X03476Y12279

There is an implied decimal after the second digit, so the numeric values of the above are:

X10.345Y06.523
X03.476Y12.279

In the final data, it does not matter if the decimal is present or not as long as there are 5 digits in the final number. Leading and trailing zeroes can not be removed.

The data that I have been supplied is in the following format:

X076299Y101900
X067450Y102083

which is the equivalent of

X07.6299Y10.1900
X06.7450Y10.2083

I need to round the data so that it is back to only 5 digits (3 places after the decimal).

The result I would want for the above numbers is:

X07.630Y10.190
X06.745Y10.208

or without the decimal point

X07630Y10190
X06745Y10208

I need the numbers rounded up or down as appropriate to the nearest thousandth and I need to keep leading and trailing zeroes to retain the 5 digit format.

Currently, I have the X and Y coordinates split into individual columns in Excel. I've managed to get the leading zeroes to stay by specifying a custom type with the format "000000". I have tried a few things with the ROUND function, but it is not giving me the results that I want.

Any help would be greatly appreciated.

 

[FormR]

Hi, welcome to the forum! Here's some options you could consider.

Excel 2013
	A	B	C	D	E
1	X076299Y101900	X07.630	Y10.190	X07.630Y10.190	X07630Y10190
2	X067450Y102083	X06.745	Y10.208	X06.745Y10.208	X06745Y10208
Sheet1

Cell Formulas
Range	Formula
B1	=TEXT(ROUND(TEXT(MID(A1,2,6),"00\.0000"),3),"\X00.000")
C1	=TEXT(ROUND(TEXT(RIGHT(A1,6),"00\.0000"),3),"\Y00.000")
D1	=B1&C1
E1	=SUBSTITUTE(D1,".","")

 

[Rick Rothstein]

Worksheet Formulas Cell Formula B1 =TEXT(ROUND(TEXT(MID(A1,2,6),"00\.0000"),3),"\X00.000") C1 =TEXT(ROUND(TEXT(RIGHT(A1,6),"00\.0000"),3),"\Y00.000") D1 =B1&C1 E1 =SUBSTITUTE(D1,".","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Why not just these for the first two formula...

B1: =TEXT(MID(A1,2,6)/10000,"\X00.000")

C1: =TEXT(RIGHT(A1,6)/10000,"\Y00.000")

 

[ExcelMK]

Thanks to both of you. It worked perfectly. I wouldn't have gotten there without your help.