1

I would like to perform a rather complicated piece of search on VBA. What I want to look for is for anything in my column that contains a "." (period/full stop) followed by a single digit.

I.e. anything that looks like this xxxx.0xxx, or xx.1.xxx ... etc

Note that the full stop can appear anywhere in the string.

To look for a full stop in string is relatively straightforward, I stick in a For loop to go through Column:

If InStr(1, String.value, ".") Then
     'do vba code
End If

The challenge is to how to isolate and identify the single character that is followed by the full stop.

When I use this formula, I am able to get the full stop and what is next to it.. But just the character next to it:

Mid(String.value, InStr(1, String.value, "."), 2)

Can you guys please advise what is the best method to proceed?

Edit

Sample data and outcome:

enter image description here

miken32
  • 42,008
  • 16
  • 111
  • 154
Oday Salim
  • 1,129
  • 3
  • 23
  • 46

1 Answers1

2

Perhaps the fastest solution would be to just increase the offset of your Mid to move past the period:

result = Mid(String.value, InStr(1, String.value, ".") + 1, 1)

Then check if result is a number.

A more powerful option would be to use regular expressions. Once enabled, you could do something like this:

Dim regex As New RegExp

regex.Pattern = "\.([0-9])"
If regex.test(String.value) Then
    theNumber = $1
End If
miken32
  • 42,008
  • 16
  • 111
  • 154