Extracting numbers from a string in Excel.

Thread: Extracting numbers from a string in Excel.

  1. WillD's Avatar

    WillD said:

    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. chizh's Avatar

    chizh said:

    Default Re: Extracting numbers from a string in Excel.

    Always a g?
    =SUBSTITUTE(A1,"g","")
     
  3. prezzy's Avatar

    prezzy said:

    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

     
  4. WillD's Avatar

    WillD said:

    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. pattikins's Avatar

    pattikins said:

    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. WillD's Avatar

    WillD said:

    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.