SUBSTITUTE a Blank space...

[Aitch]

If I have a cell with two digits - how can I use the SUBSTITUTE function to add an extra character to make the cell have 3 digits?

So a cell with 11 would turn in to 011 or A11

Thanks for your help!

 

[Rick Rothstein]

If I have a cell with two digits - how can I use the SUBSTITUTE function to add an extra character to make the cell have 3 digits?

So a cell with 11 would turn in to 011 or A11

You can use the TEXT function. For your first value...

=TEXT(B1,"000")

For your second value...

=TEXT(B1,"\A00")

or this...

=TEXT(B1,"""A""00")

 

[Aitch]

Great, thanks!

How can I use this with an IF function so it only operates on cells with 2 digits and not on cells with 3 digits?

 

[Rick Rothstein]

How can I use this with an IF function so it only operates on cells with 2 digits and not on cells with 3 digits?

Assuming the only thing in the cell are whole numbers...

=If(B1<100,TEXT(B1,"000"),"")

or

=If(B1<100,TEXT(B1,"\A00"),"")

 

[Aitch]

Unfortunately there will be letters in some of the cells

The format being plain text seems to break it - is there another way?

 

[Rick Rothstein]

Unfortunately there will be letters in some of the cells

You have to tell us this stuff... remember, we know nothing about your data except what you tell us.

See it this works for you...

=IF(AND(LEN(B1)=2,ISNUMBER(-B1)),TEXT(B1,"000"),"")

or this...

=IF(AND(LEN(B1)=2,ISNUMBER(-B1)),TEXT(B1,"\A00"),"")

 

[Aitch]

Sorry for being so vague!

This works great, thank you!

 

[Aitch]

Sorry for bothering you again!

The formula works great - except when the cell contains a number that begins with zero...

So 50 becomes A50 - but 05 stays the same, instead of becoming A05

Is there a way to fix this?

Thanks!

 

[DanteAmor]

The formula works if you have the text 05

Review the following:

Or explain with images exactly what data you have, remember, as Rick said, we can not see your data.

 

[Aitch]

Thanks for the reply!

I've checked that the formula is text, and it's still not working...

This is the formula customised for my spreadsheet - is there a mistake in it?

=IF(AND(LEN(O31)=1,ISNUMBER(-O31)),TEXT(O31,"\OO0"),IF(AND(LEN(O31)=2,ISNUMBER(-O31)),TEXT(O31,"\O0"),O31)))))