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"))
Hello Experts, thank you for the help! I like all the formulas. Remove is interesting indeed and I try to use it in my excel file and I get a #name? I dont see any code so not sure why it wont work in my file. I imagine the lamba is being used but I dont see where it is.
Also, I need to use this formula in a much larger formula and grateful for your assistance.
In the formula below, C11 is the invoice number that I need to strip I need to replace C11 with the formula.
I thought I could simply replace C11 with the formula but I dont think I can do that?
original formula:
FYI: D11 is the invoice amount.
=IF(TRIM(CLEAN(C11))="","",IF(ISNA(VLOOKUP(C11&D11,Project_Costs[[#All],[Invoice No Stripped]]&Project_Costs[[#Headers],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
I tried as follows:
using Remove:
=IF(TRIM(CLEAN(C11))="","",IF(ISNA(VLOOKUP(Remove(C11,"$%()/\|#-. ")&D11,Project_Costs[[#All],[Invoice No Stripped]]&Project_Costs[[#Headers],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
using substitute:
=IF(TRIM(CLEAN(C12))="","",IF(ISNA(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C12,"-",""),"/",""),"\",""),".",""),",",""),"(",""),")",""),"#","")," ","")&D12,ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice '#]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))
thank you very much.
I would post the file but there is a power query in it so I hope by giving the formula is enough information.
=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"))