Excel – Converting bar-codes to specific format using IF, FIND & MID function of excel

microsoft excelmicrosoft-excel-2013worksheet-function

In Excel I have a number of columns containing characters of different types such as:

WS-S5-S-L1-C31-F-U5-S9-P14 
WS-S5-S-L1-C31-F-U5-S8-P1 
WS-S5-N-L1-C29-V-U16-S6-P6

I want to convert these to 8 characters using the following rules:

  • keep only the last three segments
  • remove the U and add prefix 0 where appropriate
  • remove S and add prefix 0 where appropriate
  • remove P and add prefix 0 where appropriate

For example:

  • WS-S5-S-L1-C31-F-U5-S9-P14 convert to 05-09-14
  • WS-S5-S-L1-C31-F-U5-S8-P1 convert to 05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 convert to 16-06-06

I believe there is a way to use IF, FIND & MID function to convert these in Excel but don't know how to start. Any help will be much appreciated.

Update

Just finally, I wanted to convert this into 13 characters if possible for example:

  • WS-S5-S-L1-C31-F-U5-S9-P14 convert to S1-F-05-09-14
  • WS-S5-N-L2-C31-D-U5-S8-P1 convert to N2-D-05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 conver to N1-V-16-06-06

Best Answer

As @ygaft pointed out, it's possible, but going to be long with standard Excel functions.

I use free RegEx Find/Replace add-in in situation like that, using a regular expression you can achieve it easier.

The formula:
=RegExReplace(RegExReplace(A1,".*U([0-9]+)-S([0-9]+)-P([0-9]+)","0$1-0$2-0$3"),"0([0-9]{2})","$1")

How it works:

  • inner function:
    • A1: from content of A1 cell
    • ".*U([0-9]+)-S([0-9]+)-P([0-9]+)" look for a pattern "...U#-S#-P#" where "#" represents one or more numbers and remembers the numbers (brackets create reference groups)
    • "0$1-0$2-0$3" merges the numbers found in previous step, adding leading 0 to all of them.
  • outer function:
    • RegExReplace(...) - works with results of inner function
    • "0([0-9]{2})" - looks for 0 followed by two digits (= cases where leading 0 is not necessary)
    • "$1" - keeps only the two digits, dropping leading 0 (only in cases which were matched in previous step)

enter image description here

You can also see more explanation on the regular expressions online:

Note: I'm not affiliated in any way with that add-in, just use it as it makes my life easier.

Update

You can use this formula for your 13 character code:
=RegExReplace(RegExReplace(A3,".*-([A-Z])-[A-Z]([0-9]).*-([A-Z])-U([0-9]+)-S([0-9]+)-P([0-9]+)","$1$2-$3-0$4-0$5-0$6"),"0([0-9]{2})","$1")

Related Question