Adding periods (full stops) to the middle of numbers

[Derek M]

Hi all

Heres my question and i wonder if someone can help

I need to change this number for example 52253580000

into this number for example 52.2535.8000.00

The full stops are after 2 digits then 4 digits then 4 digits then 2 digits

Is there a way of doinf this as i have 3000 odd lines to convert

Thanks in advance

Derek

 

[Jacob Skaria]

If you mean as text then try the formula//

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

If this post helps click Yes

 

[Derek M]

Unbeilevable!!!! thank you so much, i really appreciate it

Derek

 

[Glenn]

Hi all

Heres my question and i wonder if someone can help

I need to change this number for example 52253580000

into this number for example 52.2535.8000.00

The full stops are after 2 digits then 4 digits then 4 digits then 2 digits

Is there a way of doinf this as i have 3000 odd lines to convert

Thanks in advance

Derek

You specify a total of 12 digits (2+4+4+2) but your example is only 11. Was
that a mistake, or are you expecting to pad the end with zeros?

 

[Glenn]

Or something like this:

=SUBSTITUTE(TEXT(A2,"##-####-####-##"),"-",".")

 

[Rick Rothstein]

Or even this way...

=TEXT(A1,"00\.0000\.0000\.00")

 

[Rick Rothstein]

I just noticed the typo the Glenn pointed out. Where did you want the
missing zero placed... at the beginning (if so, then my formula works fine)
or at the end? I would note that Jacob's solution does not work for the
number you posted, so I am guessing that you actually have a 12-digit number
and that the 11-digit number you posted was a typo (I think this because you
said Jacob's formula worked for you). My formula will always work for
12-digit numbers and it will work for a number composed of a smaller number
of digits as long as you want the missing digits to be zero filled at the
beginning.