+ Reply to Thread
Results 1 to 7 of 7

Extracting specific digit from string??

  1. #1
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Wisbech England
    MS-Off Ver
    Excel 2010
    Posts
    308

    Extracting specific digit from string??

    Hi All
    I am hoping that one of you clever people will be able to help me :-)
    I need to excctract a specific number from text string.
    The only problem is that this single number is within other numbers and letters.
    The only identifier is letter v .
    Example: WSOPT0202 -Issuing of Controlled documents v 6 160616
    In above sample you can see that number 6 is next to letter v and thats the number I need to extract...
    Due to inconsistancy in those string, some of them will have letter v togeter with number so that makes it more complicated :-( see example 2: WSOPT0216 - Hot Box v4 090615
    Is this still possible?
    I would appriciate your help.

    Kind Regards
    Dan

  2. #2
    Registered User
    Join Date
    06-15-2016
    Location
    here, there
    MS-Off Ver
    2010
    Posts
    59

    Re: Extracting specific digit from string??

    assuming that the text string is in A1 then put into B1
    Please Login or Register  to view this content.

  3. #3
    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: Extracting specific digit from string??

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 WSOPT0202 -Issuing of Controlled documents v 6 160616 6
    2 WSOPT0216 - Hot Box v4 090615 4

    Please see a an improved formula below in post#5
    Last edited by AlKey; 06-21-2016 at 01:15 PM.
    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

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Extracting specific digit from string??

    It seems like using the v as the constant parameter will be prone to error, with the likelyhood of a v appearing in other unrelated parts of the text.

    Would it be fair to say you want the number before the LAST Space in the string ?
    If the v and the number could be together with no space, is there at least always a space before the v ?
    Is it always a single digit number after the v ?
    Last edited by Jonmo1; 06-21-2016 at 08:18 AM.

  5. #5
    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: Extracting specific digit from string??

    I think this one it should give better chances for accuracy.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Extracting specific digit from string??

    Based on my questions inpost #4

    If the number you're looking for IS always a single digit, and it appears before the LAST space in the cell.

    Try

    =RIGHT(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))+0

  7. #7
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Wisbech England
    MS-Off Ver
    Excel 2010
    Posts
    308

    Re: Extracting specific digit from string??

    Thank You all for contributing to this post :-)
    A lot of you have provided solutions that worked and I am grateful for this.
    I have used AlKey solution as it was most suitable.

    Thank you once again.
    Dan

+ 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. [SOLVED] Extracting string between two specific delimiters among many of the same character.
    By DaniLucas in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-18-2016, 12:48 PM
  2. Replies: 8
    Last Post: 01-07-2016, 11:51 AM
  3. [SOLVED] Extracting Specific Values from a Text string
    By kollur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-04-2015, 02:22 PM
  4. Extracting Specific Words from string
    By janagan in forum Excel General
    Replies: 3
    Last Post: 08-23-2013, 12:57 AM
  5. [SOLVED] Extracting specific part of the text string to another column
    By ssanjju in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-20-2013, 07:20 PM
  6. [SOLVED] Extracting a specific string from all cells?
    By wearwestern in forum Excel General
    Replies: 3
    Last Post: 10-25-2012, 03:22 PM
  7. extracting a specific value from a string
    By xiao_gosu in forum Excel General
    Replies: 16
    Last Post: 04-24-2010, 03:38 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