Replacing First Two Numbers of a Barcode with 2 Letters

Sam Lugiano

Hi,

I would like to replace the first two numbers in column BARCODE 1 with two letters as in column BARCODE 2. For example, "10" in 104333 equals "SP", "20" in 2010651 equals "WA".

Thanks for your help!

Protonsponge

Hello @Sam Lugiano,

The REPLACE function could be helpful here but I think the community would need to know any logic/rules around the two letters associated with each bar code to give you a solid solution.

This basic demo below shows how REPLACE can be used to replace characters in a string but I suspect we will need to build in some IF statements or other functions to get this working efficiently for you.

If you can share some more info on the 2 letter rules or logic, I'm sure people here will be happy to build out a formula for you.

Protonsponge

Sam Lugiano

Hi @Paul Newcome,

Thanks for your solution. I ended up coming up with the following solution last night that seems to work.

IF(LEFT(BARCODE@row, 2) = "50", "NB" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "20", "WA" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "30", "BS" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "60", "CC" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "40", "LA" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "10", "SP" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), BARCODE@row)))))

I appreciate your help. You have also helped me out in the past with other issues.

Sam

Protonsponge

Hello @Sam Lugiano,

The REPLACE function could be helpful here but I think the community would need to know any logic/rules around the two letters associated with each bar code to give you a solid solution.

This basic demo below shows how REPLACE can be used to replace characters in a string but I suspect we will need to build in some IF statements or other functions to get this working efficiently for you.

If you can share some more info on the 2 letter rules or logic, I'm sure people here will be happy to build out a formula for you.

Protonsponge

Sam Lugiano

Hi @Protonsponge,

Thank you for the response and yes, more logic is required. Forgot to include that. I tried various combinations of REPLACE, CONTAINS and IF but no luck. The logic behind the replacement is: If prefix is "10" it should be replaced with "SP", If "20", replaced with "WA", If "30" replaced with "BS" and so on. So the first row should convert "104333" to "SP4333".

Thanks so much for the help

Sam

Paul Newcome

I would suggest creating a table that has the numbers in one column and the letters in the other. Then you can use an INDEX/MATCH to bring over the appropriate letters based on the numbers.

=REPLACE([Barcode 1]@row, 1, 2, INDEX({Letters Column}, MATCH(LEFT([Barcode 1]@row, 2), {Numbers Column}, 0)))

Sam Lugiano

Hi @Paul Newcome,

Thanks for your solution. I ended up coming up with the following solution last night that seems to work.

IF(LEFT(BARCODE@row, 2) = "50", "NB" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "20", "WA" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "30", "BS" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "60", "CC" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "40", "LA" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), IF(LEFT(BARCODE@row, 2) = "10", "SP" + MID(BARCODE@row, 3, LEN(BARCODE@row) - 2), BARCODE@row)))))

I appreciate your help. You have also helped me out in the past with other issues.

Sam