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:
- Assuming your original number is in cell A1, use the LEFT function to extract the first two digits:
- Use the MID function to extract the next four digits:
- Use the MID function again to extract the next four digits after the first four:
- Use the RIGHT function to extract the last two digits:
- 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.