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:
remove the U and prefix 0 where appropriate
remove S and prefix 0 where appropriate
remove P and 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.
Bookmarks