HI Team,
I have a series of numbers from C1 to C500 that start in cell D2.
Issue is I am trying to run a formula that has the logic:
If there are less and 4 characters in this cell then add "0" after the 1st character and replace the second to fourth characters after the newly inserted "0"
Example:
C33 - formula identifies it has less than 4 characters.
Formula then inserts a 0 after the first character: C0
and then replaces the remainin characters after the newly inserted character: C033.
Technically, the logic is possible via several different formulas and this is where i got to in regards to construction:
=IF(LEN(D2)<4),(LEFT(D2,1) & "0" MID(D2,2,4)),D2) or =IF(ISNUMBER(SEARCH("TRUE",D2)Left(D2,1) & "0" & MID(D2,2,4)
They obviously do not work as of yet.
But separately they do. I have 3 formulas. The IF formula to identifty true/false. =LEN formula to identify if a cell has less than X amount of characters and =LEFT(D2,1) & "0" & MID(D2,2,4) which inserts the "0" where i need it to go.
=IF function obviously works based on true and false statements which logically means =LEN(D2)<4 should be able to work alongside it.
Furthermore, if I can identify an If True, then it should be able to be coupled with the 3rd formula to say if true then insert character here.
Lastly, if false the cell should be left alone.
So if I had C223 and applied the formula it would be left a C223 for example.
Bookmarks