Forum Discussion
Replace Function (or Substitute) - slashes, dashes...etc
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"))
=IF(TRIM(CLEAN(C11))="","",IF(ISNA(VLOOKUP(
CONCAT(TEXTSPLIT(C11,{",","/","\","-",".","(",")","#"," "}))&D11,Project_Costs[[#All],[Invoice No Stripped]]&Project_Costs[[#Headers],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
Hi Mtarler, thank you for the follow up. It seems to work but I noticed in my testing that I need to remove leading Zeros since my [Invoice No Stripped] strips out the leading zero's and I compare the 2 data sets (old data w 1000's of rows vs new data) and one has leading zeros and the other doesnt.
here is how I have it:
=IF(TRIM(CLEAN(C11))="","",IF(ISNA(VLOOKUP(
CONCAT(TEXTSPLIT(C11,{",","/","\","-",".","(",")","#"," "}))&D11,Project_Costs[[#All],[Invoice No Stripped]]&Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
Would you happen to know how to add that part to the above? thanks again for the expert help. Much appreciated.
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"))mtarler, that is amazing. I have done my testing and it works! I have never used Lambda. Didnt even know it existed. You are very talented. Thank you very much for the help. I am very grateful. Have a good night.
- you're very welcome. Lambda is relatively new and so I think many if not most casual users don't know about it yet. It is very powerful addition as you can see. There are also a number of other new functionality that has been introduced that if you don't know about I recommend you check out including dynamic arrays, FILTER(), LET(), the new XLOOKUP and XMATCH, the LAMBDA helper functions and the new text functions like the TEXTSPLIT() function I used. It is very exciting to be an excel user right now (and no i don't work for MS and I am NOT paid by them in any way)