Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[Lessn]

the entry date will always appear in the same place at the beginning of the paragraph, all other numbers and text do not always appear in the same order / place, and the 10-digit number to be extracted will generally start with a 1######### or a 2#########.

 

[Rick Rothstein]

the entry date will always appear in the same place at the beginning of the paragraph, all other numbers and text do not always appear in the same order / place, and the 10-digit number to be extracted will generally start with a 1######### or a 2#########.

Can you use VBA code... a UDF (user defined function)... for your solution? If so...

Function Get10Digits(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) - 10
    If Mid(S, X, 10) Like "##########" Then
      Get10Digits = Mid(S, X, 10)
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Get10Digits just like it was a built-in Excel function. For example,

=Get10Digits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

.
Note the change I made in red below...

can you use vba code... A udf (user defined function)... For your solution? If so...

function get10digits(s as string) as string
  dim x as long
  for x = 1 to len(s) - 9
    if mid(s, x, 10) like "##########" then
      get10digits = mid(s, x, 10)
      exit function
    end if
  next
end function

how to install udfs
------------------------------------
if you are new to udfs, they are easy to install and use. To install it, simply press alt+f11 to go into the vb editor and, once there, click insert/module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... You are done. You can now use get10digits just like it was a built-in excel function. For example,

=get10digits(a1)

if you are using xl2007 or above, make sure you save your file as an "excel macro-enabled workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "ok" (depending on the button label for your version of excel) the next time you open your workbook.

 

[Domenic]

Thanks in part to XOR LX for the "**0" bit in the formula, here's a solution that uses native functions...

=LOOKUP(9.99999999999999E+307,(MID(SUBSTITUTE(A1," ","x"),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A1)-10+1)),10)&"**0")+0)

Hope this helps!

 

[István Hirsch]

In the strings may numbers occur which are longer than 10 digits?

 

[Lil Hotpocket]

cAN YOU BUILD THIS SO IT WILL INCLUDE NEGATIVE NUMBERS AS WELL AS THE THIRD NUMBER IN THE SEQUENSE.
so it would get -64; in 4(X^2)-12x-64=0
and could u clarify how to input your formua into excel, it wasn't working for me when i tried earlier

the formula in #23

 

[naresh0876]

I have dimensions filled in a column extracted from image files as below


eg :
[TABLE="width: 90"]
<tbody>[TR]
[TD]‪300x61‬[/TD]
[/TR]
[TR]
[TD]‪295x155‬[/TD]
[/TR]
[TR]
[TD]‪300x300‬[/TD]
[/TR]
[TR]
[TD]‪300x107‬[/TD]
[/TR]
</tbody>[/TABLE]

It could be three numbers X Three Numbers or two numbers X three numbers or Variants of these .

Would like to extract the numbers before x to a cell and numbers after x to a different cell and those values should be in numbers. Do not want to carry any spaces or text as the formula's querying the data in new cells are failing if there are spaces in the cells.

Can any one help with the formula that would help to extract only numbers to two different cells.

I tried some formula's but able to extract number before x but not after x.

Actual Formula


B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--MID("|"&A1,ROW($1:$25),1))=0)*ISNUMBER(--MID("|"&A1,ROW($2:$26),1))),ROW($2:$26)),C1),ROW($1:$25)))




First number -- works after replacing c1 above to 1 , but if replace c1 to 2 , does't yield the second number.


B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--MID("|"&A1,ROW($1:$25),1))=0)*ISNUMBER(--MID("|"&A1,ROW($2:$26),1))),ROW($2:$26)),1),ROW($1:$25)))

 

[XOR LX]

Hi.

You appear to be using formulas far more complicated than are necessary.

=0+LEFT(A1,FIND("x",A1)-1)

and

=0+MID(A1,FIND("x",A1)+1,99)

should suffice.

Regards

 

[naresh0876]

Hi.

You appear to be using formulas far more complicated than are necessary.

=0+LEFT(A1,FIND("x",A1)-1)

and

=0+MID(A1,FIND("x",A1)+1,99)

should suffice.

Regards

Thanks for your reply.But the second formula is not extracting the right side numbers.Its coming up as #value.can you help me out.

 

[XOR LX]

Can you give an example or two of strings which give that error?

Regards