Share via

remove decimal but keep leading zero

Anonymous
2023-02-22T06:16:20+00:00

I have a string of numbers like so in K3 down:

0101.31.00.99

I have this in L to remove decimals and leave me with only the numerical characters.

If the leading zero is stripped, then i am manually adding it back on by using concatenate.

IF(LEN(SUBSTITUTE(K3,".",""))=9,CONCATENATE(0,K3),(SUBSTITUTE(K3,".","")))

This works when i manually drag down formula but, when I use a macro to fill down, I lose the leading zero again.

Looking to keep leading zero without just formatting to show. I use the results to eventually do a lookup based on all 10 characters being there.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-22T07:20:51+00:00

    Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

    modify your current formula to include a TEXT function with a format code of "0000.00.00.00". This will force Excel to display the leading zero even if it's stripped by the SUBSTITUTE function.

    =IF(LEN(SUBSTITUTE(K3,".",""))=9, CONCATENATE("0", ".", TEXT(SUBSTITUTE(K3,".",""),"0000.00.00.00")), TEXT(SUBSTITUTE(K3,".",""),"0000.00.00.00"))

    Was this answer helpful?

    0 comments