Close

Results 1 to 6 of 6
  1. #1
    DF MaSter WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    70
    Thanks
    29
    Thanked:        19
    Karma Level
    96

    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 PlaYa chizh's Avatar
    Join Date
    Oct 2000
    Location
    Manchester
    Posts
    591
    Thanks
    30
    Thanked:        109
    Karma Level
    258

    Default Re: Extracting numbers from a string in Excel.

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

    Thanks to chizh

    WillD (3 Weeks Ago) 


  3. #3
    DF PwNagE prezzy's Avatar
    Join Date
    Sep 2007
    Location
    Lancashire
    Posts
    8,109
    Thanks
    711
    Thanked:        964
    Karma Level
    1327

    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 (3 Weeks Ago) 


  4. #4
    DF MaSter WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    70
    Thanks
    29
    Thanked:        19
    Karma Level
    96

    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 PlaYa pattikins's Avatar
    Join Date
    Jul 2001
    Location
    manchester
    Posts
    795
    Thanks
    251
    Thanked:        81
    Karma Level
    285

    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 MaSter WillD's Avatar
    Join Date
    Sep 2011
    Location
    Barcelona
    Posts
    70
    Thanks
    29
    Thanked:        19
    Karma Level
    96

    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.

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
  •