restructure text format

[babychai]

Hi,

Restructure format.png

Column A is the original text and i want to convert it like in Column C.

In order to do that I need to
1) remove wording "NO", for example in cell A2, A3 and A4
2) If the number is single digit then will add "0" to become at least 2 digit number. For example if the number is 1 then will become 01 and A9 will become A09.

Thank you.

[PaulM100]

Here is a crazy long formula and most likely there are better solutions:

Please Login or Register  to view this content.

[Fluff13]

Another option
=IF(LEFT(A2,2)="NO",TEXT(SUBSTITUTE(A2,"NO",""),"00"),IF(LEN(A2)=2,REPLACE(A2,2,0,0),A2))

[babychai]

Hi,

PaulM100, yeah the formula is long but still worth to understand it as well. might useful in my future work.
Fluff13, this formula is short and sweet. no doubt this formula give me the expected result.

Thanks both. really appreciate your help

[Fluff13]

You're welcome & thanks for the feedback.