View Full Version : How to isolate a numeric part from a cell?
frade
08-09-2006, 01:30 AM
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
08-09-2006, 03:43 AM
Is this what you want?
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)
frade
08-09-2006, 05:18 AM
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
08-09-2006, 06:17 AM
The formula xld provided works. Do you want to remove the dashes " - " as well? What results you are receiving?
frade
08-09-2006, 06:46 AM
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
08-09-2006, 06:57 AM
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.
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
08-09-2006, 07:48 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.