View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein Rick Rothstein is offline
external usenet poster
Posts: 5,934
Default Adding periods (full stops) to the middle of numbers

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

Rick (MVP - Excel)

"Rick Rothstein" wrote in message
Or even this way...


Rick (MVP - Excel)

"Jacob Skaria" wrote in message
If you mean as text then try the formula//

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

If this post helps click Yes
Jacob Skaria

"Derek M" wrote:

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

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

Thanks in advance