CODEWITHSUNDEEP
excelexcel-formulaformatting
aLearningLady
aLearningLady

Reputation: 2088

Excel Custom Formatting and/or Formula

I have a list of numbers for a client like below:

8481.80.9005
8481.80.9005    
8413309090
8413309090

The first two numbers exhibit the correct formatting, per the client. Is there a formula and/or custom formatting that I can use to make all numbers uniformly the correct format?

Note - all numbers are 10 digits with periods after the fourth and sixth digits.

Upvotes: 1

Views: 41

Answers (2)

Scott Craner
Scott Craner

Reputation: 152595

The problem with a custom format is that Excel will read the number as a whole number and any attempt at putting in the ., if your local settings are set to the . being the decimal separator, is that it will try to put it at the end.

So we need to first make it a string with some other separator and then do a substitute.

The following formula does that:

=SUBSTITUTE(TEXT(A1,"0000-00-0000"),"-",".")

enter image description here

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34285

I know I'm going to be shot down on this one, but couldn't see why you can't do

0000\.00\.0000

in custom formatting

Upvotes: 2

Related Questions