+ Reply to Thread
Results 1 to 10 of 10

Converting characters to specific format using IF, FIND & MID function

  1. #1
    Registered User
    Join Date
    05-23-2018
    Location
    Australia
    MS-Off Ver
    Office 2013
    Posts
    3

    Converting characters to specific format using IF, FIND & MID 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:

    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.

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Converting characters to specific format using IF, FIND & MID function

    See if this gets you started. With your data in A1;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Converting characters to specific format using IF, FIND & MID function

    Next try;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And so on.

  4. #4
    Registered User
    Join Date
    05-23-2018
    Location
    Australia
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Converting characters to specific format using IF, FIND & MID function

    I've used the following formula which returns "05" from the column 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))

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Converting characters to specific format using IF, FIND & MID function

    Are you trying to get a date from the end of the string?

    Is it dd-mm-yy format?

    Regards,

    David

  6. #6
    Registered User
    Join Date
    05-23-2018
    Location
    Australia
    MS-Off Ver
    Office 2013
    Posts
    3
    Hi David, I am simply trying to convert
    U5-S9-P14 to 05-09-14

    and I don't want to change it to a date format.

    So far, I have got the "05-" right, using the formula I listed in the previous post but I don't know how to get the entire conversion of U5-S9-P14 to 05-09-14.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Converting characters to specific format using IF, FIND & MID function

    I prefer to break it down into stages with helper columns.

    However, you could do something like...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  8. #8
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Converting characters to specific format using IF, FIND & MID function

    Was about to reply today, but I notice @CK76 has done the same thing I was going to recommend.

    I strongly agree with CK76 about using "helper" columns. Too many users avoid them for "cosmetic" reasons I do not understand!

    If you look at the formulas in Col B:F, they are compact, easy to understand and easy to edit. The "single cell" formula in Col H is a monster! I guarantee that a few weeks after it was written, you'd have great difficulty working out what you were doing should anything go wrong.

    Helper cells can be hidden anywhere in the workbook. I usually put them well off screen (say AA:AE) or on another sheet which can be hidden.

    Well done CK76. I, too, would use the Col G solution.

    Regards, David

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting characters to specific format using IF, FIND & MID function

    Try this
    Enter formula in B1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 WS-S5-S-L1-C31-F-U5-S9-P14 05-09-14
    2 WS-S5-S-L1-C31-F-U5-S8-P1 05-08-01
    3 U5-S9-P14 05-09-14
    4 WS-S5-N-L1-C29-V-U16-S6-P6 16-06-06
    Last edited by AlKey; 05-24-2018 at 10:48 PM. Reason: REPLACED WITH A SHORTER FORMULA
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  10. #10
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Converting characters to specific format using IF, FIND & MID function

    @AlKey suggested using the date formatting, which was a thought I had. Would this still work if the first two numbers were greater than 12 or 31?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Format cells containing specific characters
    By 2vbr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2018, 06:51 AM
  2. [SOLVED] Find specific text and substitute characters
    By Ellen 2Excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-15-2015, 05:50 PM
  3. Replies: 3
    Last Post: 11-12-2012, 01:52 PM
  4. Replies: 1
    Last Post: 07-19-2012, 02:27 PM
  5. Find and replace specific characters within data
    By geosteve in forum Excel General
    Replies: 1
    Last Post: 01-17-2012, 05:07 PM
  6. find and replace specific characters
    By wkilc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-21-2010, 02:59 PM
  7. find cells that contain specific characters
    By mshornet in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-23-2005, 10:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1