Extract A String Between Two Characters In Excel

Question

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?

score 1 · Answer 1 · answered Apr 28 '16 at 12:45

1

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.

answered Apr 28 '16 at 12:45

Gary's Student

95,722
10
59
99

I need the r to be String and return as String if possible – J V Apr 28 '16 at 13:09
@JV I do not understand. – Gary's Student Apr 28 '16 at 13:19

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

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

*Code is exact copy of this SO answer

edited May 23 '17 at 10:33

Community

1
1

answered Apr 28 '16 at 12:38

Dev

149
1
1
11

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. – J V Apr 28 '16 at 13:09
You could keep a counter variable and increment it in the For loop inside the if condition. – Dev Apr 28 '16 at 13:14
1

Or just put: `Len(myStr)` for the output. – Scott Craner Apr 28 '16 at 13:24

score 0 · Accepted Answer · answered Apr 28 '16 at 12:43

0

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

answered Apr 28 '16 at 12:43

SWa

4,343
23
40

Perfect! Thanks so much :) – J V Apr 28 '16 at 13:13
Hi, do you know of a way to make sure that the last 3 characters are numbers? – J V May 02 '16 at 09:38

score 0 · Answer 4 · answered Apr 28 '16 at 13:49

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

Extract A String Between Two Characters In Excel

4 Answers4