reading a string and only outputting the integers

bglanton

New Member
Joined
Oct 3, 2014
Messages
10
Hi I am trying to right a code that can read a set of strings that contains numbers and letters and I want to output only the numbers here is what i have

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


For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = CInt(Mid(sText, iCount, 1)) & lNum


If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))

Next iCount

ExtractNumber = lNum
End Function
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try
Code:
Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long, sText As String, lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, lCount, 1)) Then
        l = l + 1
        lNum = Mid(sText, lCount, 1) & lNum
    End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End Function
 

pike

Board Regular
Joined
Sep 16, 2007
Messages
231
or this classic worksheet function from Ron Coderre where you dont need to use a UDF. just adjust the A3 cell to suit
=SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

or
Code:
Option Explicit
Function ExtractifNumber(rCell As Range)
    Dim iCount As Integer, lNum As Long, bln As Boolean
    For iCount = 1 To Len(rCell)
        If Mid(rCell, iCount, 1) Like "#" Then bln = True: lNum = lNum & Mid(rCell, iCount, 1)
    Next iCount
    ExtractifNumber = IIf(bln, lNum, "Nil")
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top