Close

Results 1 to 6 of 6
  1. #1
    DF VIP Member WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    100
    Thanks
    65
    Thanked:        36
    Karma Level
    165

    Default Extracting numbers from a string in Excel.

    I've tried multiple solutions and none work, I'm using Excel 2010 in English on s Spanish computer, I know the decimal separator is the same as a deliminator in an imported csv file.
    For example in A1 154321g123

    How do I extract only the data as values?_

  2. #2
    DF VIP Member chizh's Avatar
    Join Date
    Oct 2000
    Location
    Manchester
    Posts
    610
    Thanks
    30
    Thanked:        131
    Karma Level
    330

    Default Re: Extracting numbers from a string in Excel.

    Always a g?
    =SUBSTITUTE(A1,"g","")

    Thanks to chizh

    WillD (1st May 2018)  


  3. #3
    DF VIP Member
    prezzy's Avatar
    Join Date
    Sep 2007
    Location
    Lancashire
    Posts
    8,135
    Thanks
    720
    Thanked:        988
    Karma Level
    1263

    Default Re: Extracting numbers from a string in Excel.

    Or if the letter is always in the same place but different then use take left for 6 and take right for 3
    Then use concatenate to join back together

    =LEFT(A1,6)
    =RIGHT(A1,3)
    =CONCATENATE(B1,C1)

    Data is in a1

    Thanks to prezzy

    WillD (1st May 2018)  


  4. #4
    DF VIP Member WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    100
    Thanks
    65
    Thanked:        36
    Karma Level
    165

    Default Re: Extracting numbers from a string in Excel.

    =SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)


    this is the answer I found as left right and mid returns only text unless it's part of a nested formula this is for the first value in a alpha numerical cell in A1

  5. #5
    DF VIP Member pattikins's Avatar
    Join Date
    Jul 2001
    Location
    manchester
    Posts
    795
    Thanks
    251
    Thanked:        81
    Karma Level
    344

    Default Re: Extracting numbers from a string in Excel.

    So long as the non-numerical values are grouped together, the following formula should work irrespective of where or how many non-numerical characters appear in the string.

    =VALUE(SUBSTITUTE(A1,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")),""))

    Is the "g" in the sample of any significance? Have you tried searching for a macro to do the same job?

  6. #6
    DF VIP Member WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    100
    Thanks
    65
    Thanked:        36
    Karma Level
    165

    Default Re: Extracting numbers from a string in Excel.

    Quote Originally Posted by pattikins View Post
    So long as the non-numerical values are grouped together, the following formula should work irrespective of where or how many non-numerical characters appear in the string.

    =VALUE(SUBSTITUTE(A1,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")),""))

    Is the "g" in the sample of any significance? Have you tried searching for a macro to do the same job?
    No significance and not necessarly in the same place.

Similar Threads

  1. [REQUEST] Excel help finding numbers total
    By Top Cat in forum PC Software
    Replies: 4
    Last Post: 19th April 2011, 08:31 PM
  2. extracting and writing the dvd key
    By dragonmiroff in forum Microsoft Consoles
    Replies: 2
    Last Post: 1st April 2010, 06:34 AM
  3. Iso Extracting help
    By Spennyboy in forum Microsoft Consoles
    Replies: 5
    Last Post: 19th January 2005, 04:32 PM
  4. atr string
    By maddy in forum Digital Satellite TV
    Replies: 0
    Last Post: 24th January 2003, 11:54 AM
  5. What's that string?
    By arrid in forum PC Problems
    Replies: 1
    Last Post: 13th November 2002, 09:25 PM

Social Networking 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
  •