Multiple formatting in one column

VBA is always the best solution to these type of matters.... But I recognize that we don't all want to do this... So Nested formulas would be my next suggestion.

Lets modify what you have stated.

First rule you imply is your false... meaning... if something else is not present leave as is...

If the part number begins with a zero that the spaces and the zero are in place and if if it's a short number like 021 that's intact and H380 etc also stay as they are

If these are your 3 Scenarios than this is your simple solution...

Please Login or Register  to view this content.

What this says is... IF the CELL starts with 0 or the Cell starts with a CHAR and not a NUM then just return the CELL otherwise custom formula (Formatting the CELL)

CELL - Should be your first cell that you find this in
CUSTOM FORMULA - Should be the formula you stated you already can do
Which I am assuming would be something like

Please Login or Register  to view this content.

I think that will do it if I read the question correctly...
Really that text formula should treat non numeric numbers as a string and not try to format it...though a short number would become a string of 0's ending with the value...

so your 043 would turn into 0 000 000 043... but with the IF statement leading it will prevent that from happening... but if you are good with this...than just go with only the text formula


Cheers