4

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
  • Are the strings always off the same length? – Kevin Anthony Oppegaard Rose May 23 '18 at 5:46
  • 1
    @Kevin: no, see "U5" vs "U16" – Máté Juhász May 23 '18 at 6:22
  • Strings are of different length and I've used the following formula which returns "05" from WS-S5-S-L1-C31-F-U5-S9-P14. But how do I return "05-09-14"? =IF(MID(E13,FIND("-U",E13)+3,1)="-","0"&MID(E13,FIND("-U",E13)+2,1),MID(E13,FIND("-U",E13)+2,2)) – Indy May 23 '18 at 7:03
6

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

  • That's a great add-in to use. Worked perfectly. 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 convert to N1-V-16-06-06 – Indy May 24 '18 at 11:32
  • how do you mean 13 characters? Please update your question and I'll update my answer – Máté Juhász May 24 '18 at 11:33
  • Basically converting: • WS-S5-S-L1-C31-F-U5-S9-P14 to S1-F-05-09-14 • WS-S5-N-L2-C31-D-U5-S8-P1 to N2-D-05-08-01 • WS-S5-N-L1-C29-V-U16-S6-P6 to N1-V-16-06-06 So using the 7th, 10th, 15,16 and 17th character – Indy May 24 '18 at 11:48
5

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")
  • great technique used but code runs a bit lengthy. – Indy May 24 '18 at 11:19
  • to make it shorter, you can add helper column with that function: '=RIGHT(A1,LEN(A1)-FIND("U",A1,1)' and then refer to that column instead all that piece of code – ygaft May 24 '18 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.