Forum Discussion

MisterBobTheTomato's avatar
MisterBobTheTomato
Copper Contributor
Nov 24, 2020

Custom Number Formatting

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 C07CR2...
excel
Formulas and Functions
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Nov 26, 2020

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",...).