Removing the last specific character from the results of my formula-CodePudding

I'm using some VLOOKUPs to pull in text from another tab on my spreadsheet using the below formula

={"Product Category Test";ARRAYFORMULA(IF(ISBLANK(A2:A),"",
VLOOKUP(A2:A,'Import Template'!A:DB,MATCH("Product Category",'Import 
Template'!A1:DB1,0),false)&"|"&IF(VLOOKUP(A2:A,'Import Template'!A:DB,MATCH("Automatic 
Categories",'Import Template'!A1:DB1,0),false)<>"",VLOOKUP(A2:A,'Import 
Template'!A:DB,MATCH("Automatic Categories",'Import Template'!A1:DB1,0),false),"")))}

Example of results: Books|Coming Soon Images|

All of my results will be delimited by a "|" which will also be the final character. I need to remove the final "|" from the results ideally without using a helper column, is there a way to wrap another function around my formula to achieve this? I've played around with RIGHT and LEN but can't figure it out.

Thanks,

CodePudding user response：

use regex:

=ARRAYFORMULA({"Product Category Test"; REGEXREPLACE(""&IF(ISBLANK(A2:A),,
VLOOKUP(A2:A,'Import Template'!A:DB,MATCH("Product Category",'Import 
Template'!A1:DB1,0),)&"|"&IF(VLOOKUP(A2:A,'Import Template'!A:DB,MATCH("Automatic 
Categories",'Import Template'!A1:DB1,0), )<>"",VLOOKUP(A2:A,'Import 
Template'!A:DB,MATCH("Automatic Categories",'Import Template'!A1:DB1,0),),)), "\|$", )})

if this won't work make sure there are no empty spaces after last |