How to isolate a numeric part from a cell?

[frade]

Hello,

I would like to isolate the numeric part of a cell in Excel.

Please have a look at the attached file.

The problem is that some characters are numerics but not all.

I didn't find any solution using classical function.

Thanks in advance for your help in VBA

Regards

Fran?ois

[Bob Phillips]

Is this what you want?

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

[frade]

Thank you

Yes, it's something like this but unfortunately the formula contains an error without any information about the kind of problem. Why?

regards

Fran?ois

[Shazam]

The formula xld provided works. Do you want to remove the dashes " - " as well? What results you are receiving?

[frade]

Yes, I would like to remove the '-' too!

I receive this msg 'invalid result'

Please have a look at the attached files (print screen/print screen 2)

Many Thanks!

Fran?ois

[Shazam]

I'm assuming that when you inputed the formula it had a back space before the equal sign. So if you click behind the equal sigh " = " and hit the button backspace it will work.

Here is a formula that will extract all numbers in a cell regardless if its front, back or mid.

Input formula in cell E1 and copy down.

=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)

The formula is an-array
Must hold down:

Crtl,Shift,Enter

Sample file below.

[fixo]

Yes, I would like to remove the '-' too!

I receive this msg 'invalid result'

Please have a look at the attached files (print screen/print screen 2)

Many Thanks!

Fran?ois

Hi, Francois
see how this will work for you:

Public Function RemSubStr(ByVal curStr As String, ByVal subStr As String, ByVal repStr As String) As String
Dim iLen, iPos As Integer
Dim tmpStr As String
iLen = Len(subStr)
iPos = InStr(curStr, subStr)
Do While iPos > 0
tmpStr = tmpStr & Left(curStr, iPos - 1) & repStr
curStr = Mid(curStr, iPos + iLen)
iPos = InStr(curStr, subStr)
Loop
RemSubStr = tmpStr & curStr
End Function
Sub RemWildcards()
Dim sourceRng As Range
Dim curRng As Range
Dim outStr, patStr As String
Dim i As Long
Set sourceRng = ActiveSheet.Range("A1:A64")
For Each curRng In sourceRng
outStr = curRng.Value
i = 1
While i <= Len(outStr)
patStr = Mid(outStr, i, 1)
If Not patStr Like "#" Or patStr Like "[-,|,:,~,/,\,^,{,}]" Then ' add wildcards you need
outStr = RemSubStr(outStr, patStr, Chr(32))
End If
i = i + 1
Wend
curRng.Value = outStr
Next
End Sub


Fatty

~'J'~

[Zack Barresse]

For VBA, take a look at these KB entries ...

http://vbaexpress.com/kb/getarticle.php?kb_id=816
http://vbaexpress.com/kb/getarticle.php?kb_id=68