Custom Number Formatting

[MisterBobTheTomato]

Good day folks! I want to create a custom format to assign to a group of cells. Thank you in advance.  Mister Bob the Tomato

I want to make entries in these two manners: D032CR20123456 and C07CR20123456

automatically convert to: D-032-CR-20-123456 and C-07-CR-20-123456

[#2]

@MisterBobTheTomato 

Here is an example with a formula.

There are many ways and ways to get there, that's just one of the many.

Formula: =MID(A1,1,1)&"-"&MID(A1,2,3) &"-"&MID(A1,5,2) &"-"& MID(A1,7,2)&"-"&MID(A1,9,6)

Result:  D-032-CR-20-123456

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@MisterBobTheTomato   To explain Nikolino's response, the point is:  this cannot be done with a Custom format per se.

Excel considers D032CR20123456 and C07CR20123456 to be text, not numbers.  Note that ISTEXT(A1) is TRUE.  And Excel does not provide any Custom formats for text, other than to display a prefix and/or suffix; e.g. Custom "prefix "@" suffix" .

You could write a macro (VBA Sub) that converts a selected range of such text, effectively applying Nikolino's suggestion in situ.

Or you could write an event macro that applies the same conversion to text when it is entered.

But in all cases, the result is different text, unlike Custom formats that merely alter the appearance of an unchanged value.  For example, instead of IF(A1="D032CR20123456",...), you would need to write IF(A1="D-032-CR-20-123456",...).