+ Reply to Thread
Results 1 to 7 of 7

extracting characters from string

  1. #1
    Registered User
    Join Date
    12-31-2016
    Location
    israel
    MS-Off Ver
    2016
    Posts
    19

    extracting characters from string

    Hi,
    i have a very long list of items, which names include their measures:
    FB_127_52
    FB_129_19
    FI_12_25
    FB_12_52
    FC_131_43
    FE_132_19
    FE_137_25
    FB_138_52
    FB_139_103

    assuming this list is in column A
    i would like to have the first number in column B, and the second number in column C
    if we take the first name as an example, i would have 127 in B1 and 52 in C1
    i could go "MID" and "RIGHT", but because the number of chars is not consistent, that won't work.
    any idea how to automatically extract these numbers?
    Thanks!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: extracting characters from string

    Which version of Excel are you using? Why not use the Text to Columns feature with the underscore as your delimiter?

    First number:

    =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),99,99))

    Second number:

    =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),198,99))
    Last edited by AliGW; 05-17-2020 at 01:26 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,298

    Re: extracting characters from string

    Try this:

    =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",250)),COLUMNS($A1:A1)*250,250))

  4. #4
    Registered User
    Join Date
    12-31-2016
    Location
    israel
    MS-Off Ver
    2016
    Posts
    19

    Re: extracting characters from string

    brilliant!
    wasn't aware of this tool
    thanks!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: extracting characters from string

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Please update your profile to show your current version of Excel.

  6. #6
    Registered User
    Join Date
    12-31-2016
    Location
    israel
    MS-Off Ver
    2016
    Posts
    19

    Re: extracting characters from string

    thank you very much!
    and this extracts the second set of unmbers: =TRIM(RIGHT(SUBSTITUTE($A1,"_",REPT(" ",250)),COLUMNS($A1:A1)*250))
    cool

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: extracting characters from string

    Yes, and you could do the same with mine:

    =TRIM(RIGHT(SUBSTITUTE($A1,"_",REPT(" ",99)),COLUMNS($A1:A1)*99,99))

    You have missed the final bit:

    =TRIM(RIGHT(SUBSTITUTE($A1,"_",REPT(" ",250)),COLUMNS($A1:A1)*250,250))

+ 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. Extracting characters from a text string - please help
    By bookharin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-23-2015, 10:51 AM
  2. [SOLVED] extracting 1st 4 characters from a string
    By braintrain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 01:48 AM
  3. [SOLVED] Extracting words from string based on certain characters...?
    By Flabbergaster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2012, 03:42 AM
  4. removing/extracting characters from a string
    By The Monitor in forum Excel General
    Replies: 1
    Last Post: 11-30-2011, 06:19 PM
  5. extracting characters from string
    By johnmerlino in forum Excel General
    Replies: 6
    Last Post: 10-21-2010, 09:56 AM
  6. Extracting characters from a string
    By incognito in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-30-2007, 03:18 AM
  7. Replies: 6
    Last Post: 10-29-2005, 09:05 PM

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