+ 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
    36

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    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
    36

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    36

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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. [SOLVED] Extracting a character from a string of characters
    By Sue in forum Excel General
    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