Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Jan 27, 2023
Solved

Replace Function (or Substitute) - slashes, dashes...etc

Hello, I need to replace various extraneous characters found in invoice numbers so I can compare based only on the letters or numbers. I need to replace the following with nothing: Dash Slash (b...
excel
  • mtarler's avatar
    mtarler
    Jan 28, 2023

    Tony2021 If you know there will be at most x number you could use nested IF() statements to remove them but a recursive LAMBDA works really well.  Go to Formulas -> Name Manager and add a NEW name called strip0 and give it this definition:

    =lambda(n, if(left(n,1)="0",strip0(mid(n,2,len(n))),n))

    As long as you are starting down this path why not go all the way and make a custom function to do it all for you.  As shown in the next image is the name manager with 4 names:

    strip0 as noted above will strip the preceding 0s off

    punctuation is just a name for the array of characters to get removed

    RemoveChars is the function I gave above that uses CONCAT and TEXTSPLIT to remove the characters

    and

    CleanUp puts them all together:

    so now you can use:

    =IF(TRIM(CLEAN(C11))="","",IF(ISNA(VLOOKUP(
CleanUp(C11)&D11,Project_Costs[[#All],[Invoice No Stripped]]&Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 29, 2023

Tony2021 

Another formula but without the elegance of mtarler 's recursive formula.  It uses a side effect of TEXTSPLIT, namely it removes the separators.

= MAP(invoiceNo, LAMBDA(inv, CONCAT(TEXTSPLIT(inv, unwanted))))
where 'unwanted' refers to
= {"-";"\";"/";".";",";"(";")";"%";"$";"|";"#";" "}

The MAP/LAMBDA calculates cell by cell but returns the entire array of results. 

Resources