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

Question

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

Answer 1

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)

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

• first step: https://regex101.com/r/4W4dWw/1
• second step: https://regex101.com/r/mA8lGs/1

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")

Answer 2

Pretty ugly,
but you can achieve it by the following, it assumes that your working data is in column A:

=TEXT(LEFT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-1),"00")&"-"&TEXT(MID(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)+2,(FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-2-FIND("S",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1))),"00")&"-"&TEXT(RIGHT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND("U",A1,1)))-FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)),"00")