Re: Extract Number from text

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

''''''''''''''''''''''''''''''''''''''''''
'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) or _
Mid(sText, iCount,1) = "." Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If

Next iCount


ExtractNumber = CDbl(lNum)
End Function

--
Regards,
Tom Ogilvy

<rrstudio2@xxxxxxxxxxx> wrote in message
news:1135706192.343932.58750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> I have cells with data such as
> 0.5 ml
> 1ml
> 560 gm
> 373 milliliters
>
> and need a function to extract the number part of these. I found a
> formula that sort of works,
> -----------------
> Function ExtractNumber(rCell As Range)
> Dim iCount As Integer, i As Integer
> Dim sText As String
> Dim lNum As String
>
> ''''''''''''''''''''''''''''''''''''''''''
> 'Written by OzGrid Business Applications
> 'www.ozgrid.com
>
> 'Extracts a number from a cell containing text and numbers.
> ''''''''''''''''''''''''''''''''''''''''''
> sText = rCell
>
> For iCount = Len(sText) To 1 Step -1
> If IsNumeric(Mid(sText, iCount, 1)) Then
> i = i + 1
> lNum = Mid(sText, iCount, 1) & lNum
> End If
>
> If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
> Next iCount
>
>
> ExtractNumber = CLng(lNum)
> End Function
> -----------------------
> However this formula seems to ignore decimal points and for example the
> 0.5 is returned as 5. All I want is a function to return just number
> part and not the units. Any ideas?
>
> -Andrew V. Romero
>


.

Relevant Pages

  • Save to a specific location based on mailmergedatafields
    ... Dim fFieldText() As String ... Dim iCount As Integer ... Dim fField As FormField ... Dim sWindowMain, sWindowMerge As String ...
    (microsoft.public.word.mailmerge.fields)
  • Re: DragDrop Outlook attachments to a listbox VB6
    ... hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As ... Dim oExplorer As Object ... Dim iCount As Integer ... If InStr= 0 Then Exit Sub ...
    (microsoft.public.vb.general.discussion)
  • Re: DragDrop Outlook attachments to a listbox VB6
    ... several attachments with the same filename. ... hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As ... Dim oExplorer As Object ... Dim iCount As Integer ...
    (microsoft.public.vb.general.discussion)
  • Re: Bounding Combo Box to a database in VB6
    ... Dim rs As DAO.Recordset, sRecordset As String ... Dim iCount As Long, i As Long ... On Error Goto FillComboErr ...
    (microsoft.public.vb.general.discussion)
  • Re: VBA for getting files off internet
    ... Add references to ADO, Microsoft XML, Microsoft Internet controls, Microsoft HTML Object library. ... Sub GetFiles(sURL As String, sExtension As String) ... Dim oDoc As New MSHTML.HTMLDocument ... Dim iCount As Integer ...
    (microsoft.public.excel.programming)