Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Adding periods (full stops) to the middle of numbers

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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Adding periods (full stops) to the middle of numbers

Adding Periods to Numbers in Excel

Hi Derek,

Yes, there is a way to add periods to the middle of numbers in Excel. You can use a combination of the LEFT, MID, and RIGHT functions to extract the different parts of the number and then concatenate them with periods in between.

Here are the steps:
  1. Assuming your original number is in cell A1, use the LEFT function to extract the first two digits:
    Formula:
    =LEFT(A1,2
  2. Use the MID function to extract the next four digits:
    Formula:
    =MID(A1,3,4
  3. Use the MID function again to extract the next four digits after the first four:
    Formula:
    =MID(A1,7,4
  4. Use the RIGHT function to extract the last two digits:
    Formula:
    =RIGHT(A1,2
  5. Concatenate all the extracted parts with periods in between:
    Formula:
    =LEFT(A1,2)&"."&MID(A1,3,4)&"."&MID(A1,7,4)&"."&RIGHT(A1,2

This formula will give you the desired output of 52.2535.8000.00 for the number 52253580000.

To apply this formula to all the cells in your worksheet, you can copy and paste the formula down the column or use the Fill Handle to drag the formula down.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Adding periods (full stops) to the middle of numbers

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 digits

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

Thanks in advance

Derek

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Adding periods (full stops) to the middle of numbers

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

Derek

"Jacob Skaria" wrote:

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 digits

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

Thanks in advance

Derek

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Adding periods (full stops) to the middle of numbers

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 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?


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Adding periods (full stops) to the middle of numbers

Or something like this:

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


Jacob Skaria wrote:
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 digits

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

Thanks in advance

Derek

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default Adding periods (full stops) to the middle of numbers

Or even this way...

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

--
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
digits

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

Thanks in advance

Derek


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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
beginning.

--
Rick (MVP - Excel)


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

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

--
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
digits

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

Thanks in advance

Derek



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
parsing a full name out into Last, First, Middle Craig Excel Worksheet Functions 2 July 29th 09 12:57 AM
Separating Full Names into First Middle and Last [email protected] Excel Discussion (Misc queries) 1 September 8th 07 02:06 AM
formula for adding x periods/mo add 11% to each nastech Excel Discussion (Misc queries) 2 April 3rd 07 06:08 PM
seperate a full name into first, middle, last, & suffix column ichihina Excel Worksheet Functions 1 February 19th 07 05:51 PM
Adding periods to sentences. RJJ Excel Discussion (Misc queries) 2 September 28th 06 11:01 PM


All times are GMT +1. The time now is 08:58 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"