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]

It is hard to know whether your formula is correct or not (aside from the 3 extra closing parentheses at the end) as we do not know exactly what you are trying to do (some clear cut before and after examples would be quite helpful). You changed from either all zeros or zeros with a leading "A" to not as many zeros as I think you want and one or two letter "O"s. Let me take a guess though... do either one of these do what you want.

=IF(AND(LEN(O31)<3,ISNUMBER(-O31)),TEXT(O31,"000"),O31)

or

=IF(AND(LEN(O31)<3,ISNUMBER(-O31)),TEXT(O31,"\A00"),O31)

 

[DanteAmor]

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)))))

To understand it is necessary that you put some examples of what you have and what you expect of result.
Now, to evaluate your formula you must give an example of each case, listing what you have and what you expect of result.

 

[Eljozi]

Hi

This may not be the best way but it is an easy solution.
you have to change the column where you have the row data to Text format instead of General and you will have the 05

 

[Aitch]

I removed the \ from the TEXT function and it seems to have solved the problem!

What does the \ do within the TEXT function? (Better check with you, in case it breaks something else!)

Thanks again!