Insert multiple decimal points

[Fishboy]

Afternoon all,

I have just imported a CSV into Excel 2010 which contains fictional sale data from my friend's online game. The value of the sales has been recorded as a long continuous string of numbers but should read as Gold, Silver and Copper coin currency, for example 10g20s44c.

For arguement's sake I can explain that 100 copper make a silver, and 100 silver make a gold.

Example data:

267556 (should read as 26.75.56)
4657185 (should read as 465.71.85)
002015 (should read as 00.20.15)
57624526 (should read as 5762.45.26)

Now, here is my problem -

I want to use a formula to insert a decimal point in between the gold, silver and copper coins as per the examples above.

I tried this first:

=LEFT(A2,2)&"."&MID(A2,3,2)&"."&MID(A2,4,2)

It turns out I can't use a formula with LEFT in, as despite the coppers and silvers always being 2 digits (00 to 99), the amount of gold can vary from a value as short at 2 digits (i.e 00 to 99) up into 3, 4 or even 5 digit numbers (i.e, hundreds, thousands or tens of thousands).

I tried amending a LEFT formula into a RIGHT formula as follows:

=RIGHT(A2,2)&"."&MID(A2,3,2)&"."&MID(A2,4,2)

I hoped that would read the long string number is A2, counting back 2 numbers from the last digit on the right and insert a decimal point (separating the coppers). Then count 2 more numbers back and add another decimal point (separating the silvers). This meant it didn't matter how big the gold value was, the decimals were always in the right place.

But there in lies the rub...

Firstly the layout of this formula brings back the last 2 digits first (i.e the coppers show at the front end, not at the back end) so a string of 102044 comes back as 44.20.04. Secondly I cannot make it count the first numbers up as far as the first decimal point at all!

I know I am just laying the formula out incorrectly, but for the life of me I am lost and scratching my head.

 

[gaz_chops]

Try
=SUBSTITUTE(A1,RIGHT(A1,4),"")&"."&MID(A1,LEN(A1)-3,2)&"."&RIGHT(A1,2)

 

[Stiuart_W]

Or:-

=IF(LEN(A1)>4,LEFT(A1,LEN(A1)-4) &"."&MID(A1,LEN(A1)-3,2)&"."&RIGHT(A1,2),"00" & LEFT(A1,LEN(A1)-4) &"."&MID(A1,LEN(A1)-3,2)&"."&RIGHT(A1,2))

 

[Fishboy]

Thanks guys. Sorry Stiuart_W but Gaz_Chops pipped you to the post by a couple of minutes.

Gaz, your formula works a treat mate.

Thanks a million!

 

[gaz_chops]

Good call didn't think if it was below 4 characters!
=IF(LEN(A5)>4,SUBSTITUTE(A5,RIGHT(A5,4),"")&"."&MID(A5,LEN(A5)-3,2)&"."&RIGHT(A5,2),"00"&"."&LEFT(A5,2)&"."&RIGHT(A5,2))

 

[gaz_chops]

You're welcome, but as Stiuart spotted, if it is possible to be below 4 characters, use my new formula or Stiuarts.

 

[acefda]

I have a similar question about inserting multiple decimal points:

In cell c10 of my spreadsheet, I have a mathematical formula resulting in the number 103.01. I need a formula for cell c11 that will generate 103.01.01. Then in c12 I need a formula that will generate 103.01.02. For c13, 103.01.03, and so on.

Thanks for any help offered!