CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Jack
Jack

Reputation: 863

Remove dot and keep leading zero in Excel

I have a column in Excel with values that looks like this:

01.01
01.02
01.03
01.04
01.05
01.06
0101.2
0101.21.00
0101.21.00.00
0101.29.00
0101.29.00.10

I am trying to remove the "." in the 4 digit cells i.e. 01.01 01.02 etc. so that they will show 0101, 0102 instead. The current cells indicated "General" format. I changed it to "text". Then tried to do a replace "." to blank. However, it will change from 01.01 to 101. I have tried several methods including "custom" format to no avail.

I filtered the column so only those cells with length=5 show up. I then applied a "custom" format 0000 to the cells. It changed. However, I then tried to sort the column, it will not sort properly. The custom formatted 4-digit cells are conflicting with other cells.

I only need to remove the "." for those 4-digit cells and keeping the leading zero. Seems like an easy task but I am pulling my hair. Any advise is appreciated.

Upvotes: 1

Views: 1686

Answers (2)

pnuts
pnuts

Reputation: 59485

Different interpretation:

=IF(MID(A1,3,1)=".",REPLACE(A1,3,1,),A1)

May sort as you require.

Change ,s for ;s if necessary for your configuration.

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

When A1 is your target cell, 

B1=SUBSTITUTE(A1,".","")

In my photo Column A is formatted as Text and Column B is good ol' fashion General

enter image description here

Upvotes: 2

Related Questions