Possible "CODE" formula enhancement/extension

[Celticshadow]

Hi Forum

I have a formula that looks at a cell with three numbers contained within and it then rearranges them to replace any fig greater than a 5 with a 0 (zero). Thus if cell contains 541 then formula will change it to 041. However I have a couple of problems I would like solving by adding to the existing formula as below

The formula I am using in my spreadsheet is -

=IF(CODE(LEFT(B5)) > 52, 0, LEFT(B5))& IF(CODE(MID(B5,2,1)) > 52, 0, MID(B5,2,1))& IF(CODE(RIGHT(B5)) > 52, 0, RIGHT(B5))

1. If there is say a 51 in the cell then the formula changes that to 011 when infact I require 01 or if it had a 77 in the cell then it would change that to 000 when infact I require 00 or 22 would be changed to 222 instead of 22 etc.

2.If there is just a single figure in the cell such as a 1 or a 2 or a 3 etc then it just shows #VALUE! in the cell, when I would like it to show it as is, ie a 1 or a 2 or a 3 etc.

Any pointers to extend enhance the formula to help solve my query will be much appreciated.

I hope the above makes some semblance of sense if not please do not hesitate to ask for further clarity.

Regards

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Celticshadow]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff

I am using office 365 and Windows 10 and have updated profile accordingly.

Regards

 

[Fluff]

Thanks for that.
How about

=LET(m,MID(B5,SEQUENCE(,LEN(B5)),1),CONCAT(IF(m+0>4,0,m)))