 # Thread: Extracting numbers from a string in Excel.

1. ## 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?_  Reply With Quote

2. ## Re: Extracting numbers from a string in Excel.

Always a g?
=SUBSTITUTE(A1,"g","")  Reply With Quote

3. ## 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  Reply With Quote

4. ## 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  Reply With Quote

5. ## 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?  Reply With Quote

6. ## Re: Extracting numbers from a string in Excel. Originally Posted by pattikins 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.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•