putting a decimal in a number that doesn't have one.

[frankday]

I have a number 099509980999. It means High 99.5% Mid 99.8% and Low 99.9% accuracy. I have been able to break it down to 0995 0998 0999 in a number format. Now I want it to look like 099.5 099.8 099.9 using a formula. I want the final product to be:

Cell A1 "High 099.5"
Cell B1 "Mid 099.8"
Cell C1 "Low 099.9"

I know I can use a & symbol to get the words in. Its the decimal I can't get in.

This is more difficult than I thought.

Can anyone help.

Thanks

[protonLeah]

What formula and in which cell?

[jtakw]

Hi,

Perhaps something like this:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	High 099.5	Mid 099.8	Low 099.9		099509980999

Sheet: Sheet14

Excel 2016 (Windows) 64 bit

	A	B	C
1	="High "&REPLACE(LEFT(E1,4),4,0,".")	="Mid "&REPLACE(MID(E1,5,4),4,0,".")	="Low "&REPLACE(RIGHT(E1,4),4,0,".")

Sheet: Sheet14

[FlameRetired]

With that 12 digit number in F1 ... you will need to enter it as text. Insert an "'" in front of the leading 0.
In A1 and filled across.

Please Login or Register  to view this content.

	A	B	C	D	E	F
1	High 099.5	Mid 099.8	Low 099.9			099509980999

[frankday]

Perfect, Thanks

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AlKey]

Here is a simplified version of FlameRetired's formula

Please Login or Register  to view this content.

[FlameRetired]

@ frankday

You're welcome. Thanks for the feedback.

@AlKey

Cool. Thanks for the backup.

[jtakw]

Here is a simplified version of FlameRetired's formula

Please Login or Register  to view this content.

I'm not so sure it's "simplified", as OP stated he wanted the results With the leading 0 as FlameRetired's version would produce.