TRANSLATE is a handy function available in some flavors of SQL to help with data cleansing
Here is a version written as an Excel UDF so that it can be used in cell formulas
Note: In this version if the character list is longer than the translation list then those are assumed to be ones you want to remove.
Please Login or Register to view this content.
A couple usage examples:
delimiter standardization
1 EA1-BX5-PK10 =translate(A1,"\-","//") EA1/BX5/PK10 2 EA1\BX5\PK10 =translate(A2,"\-","//") EA1/BX5/PK10 3 EA1/BX5/PK10 =translate(A3,"\-","//") EA1/BX5/PK10
problem characters removal/avoidance
1 description\ =translate(A1,"\|"&CHAR(13)&CHAR(9)&CHAR(10),"/"&CHAR(166)&" ") description/ 2 new
line=translate(A2,"\|"&CHAR(13)&CHAR(9)&CHAR(10),"/"&CHAR(166)&" ") new line 3 no tab =translate(A3,"\|"&CHAR(13)&CHAR(9)&CHAR(10),"/"&CHAR(166)&" ") no tab 4 pipe|to bar =translate(A4,"\|"&CHAR(13)&CHAR(9)&CHAR(10),"/"&CHAR(166)&" ") pipe¦to bar
Last edited by Gregor y; 01-19-2023 at 04:11 PM. Reason: typo, forum posting issues
Unfortunately it's not quite that simple, since it doesn't pass the translate vs replace test...
1 abcdef =translate(A1,"abc","bcd") ddddef bcddef 2 abcdef =translate2(A2,"abc","bcd") bcddef bcddef
but it can be fixed
Please Login or Register to view this content.
Thank you for sharing this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks