Removing Chinese characters from cells

[theMaj]

I have a worksheet with more than 1,000 rows of Inventory Items and in each cell there is a mix of English characters and Chinese as in:
"< Macallen Highland Malt 12 Yr 麦卡伦蓝钻12年单一麦芽苏格兰威士 >
There is no consistency of EN - CN or CN- EN and it leaves me with large column widths, not to mention that many of the translations are sloppy and incorrect. My Pivot Tables look even worse as you may imagine. I was wondering whether there is some Excel function or a simple VBA that could some how filter out the CN in an adjacent column. I'm using latest subscription Office 365

 

[duggie33]

You could try a formula like this in an adjacent column...

=TEXTJOIN("",TRUE,IF(CODE(MID(A1,SEQUENCE(LEN(A1)),1))<>63,MID(A1,SEQUENCE(LEN(A1)),1),""))

Hope that helps,

Doug

 

[theMaj]

Doug, that is ingenious....worked like a charm. Only remaining issue is 'how the hell does this work, so short & simple but I have no idea what it's doing...'? Other than some magical thing about the '>63'.
I've seen a multitude of solutions requiring the use of multiple columns (added, then hidden), and others using VBA which I really didn't want to deal with but this did exactly what I needed.
Thank you very much, made my day....

 

[Peter_SSs]

so short & simple

Instead of using TEXTJOIN with "" as the delimiter, even shorter to use CONCAT.
Try this as well.

=LET(c,MID(A1,SEQUENCE(LEN(A1)),1),CONCAT(IF(CODE(c)=63,"",c)))

 

[duggie33]

Glad we could help. The formula checks each character in the string to see if it's character code is equal to 63 and keeps only those not equal to 63.

Doug