theStr = "KT150"
Characters count is always 5 in total. I want to make sure that there is 3 numbers in theStr. How would I achieve this in Excel VBA?
4 Answers
You do not need VBA to get the number of digits in a string, but here is one way to count them:
Public Function KountNumbers(r As Range) As Long
Dim i As Long, t As String
t = r.Text
For i = 1 To Len(t)
If Mid(t, i, 1) Like "[0-9]" Then KountNumbers = KountNumbers + 1
Next i
End Function
for example:
Without VBA try this:
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))
to get the number of numeric digits.
2 Comments
J V
I need the r to be String and return as String if possible
Gary's Student
@JV I do not understand.
Your question is a little lacking in detail, but how about:
Sub test()
Debug.Print containsXnumbers("KT150", 3)
End Sub
Function containsXnumbers(sInput As String, xNumbers As Long) As Boolean
Dim x As Long
Dim numCount As Long
For x = 1 To Len(sInput)
If IsNumeric(Mid(sInput, x, 1)) Then numCount = numCount + 1
Next x
If numCount = xNumbers Then containsXnumbers = True
End Function
This should help:
Function onlyDigits(s As String) As String
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '
' Initialise return string to empty '
retval = ""
' For every character in input string, copy digits to '
' return string. '
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next
' Then return the return string. '
onlyDigits = retval
End Function
Example:
Dim myStr as String
myStr = onlyDigits ("3d1fgd4g1dg5d9gdg")
MsgBox (myStr)
Will return (in a message box):
314159
*Code is exact copy of this SO answer
3 Comments
J V
how would I add a counter for how many numbers there is? I tried the .Count func but doesn't seem to work right out of the box.
Dev
You could keep a counter variable and increment it in the For loop inside the if condition.
Scott Craner
Or just put:
Len(myStr) for the output.try with the below formula
Assume that your data are in A1. Apply the below formula in B1
=IF(AND(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0",""))=2,LEN(A1)=5),"3 character numerals","No 3 numerals found")
answered Apr 28, 2016 at 13:49
Karthick Gunasekaran
2,7231 gold badge17 silver badges26 bronze badges