Printable View
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?_
Always a g?
=SUBSTITUTE(A1,"g","")
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
=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
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.Quote:
Originally Posted by pattikins