Validate a string char-by-char

[Jaymond Flurrie]

So, I'm trying to validate a string that can have only dots and numbers, but there can't be two dots in a row. String has to begin with a number and end to a dot

So these are ok
1.1.1.1.1.1.
and
2.452443.1.5.21.5.42131.

but
1..1.
and
1.1.t.
and
.1.1.
and
1.1
are not ok.

How to do this?

 

[SteveO59L]

So, I'm trying to validate a string that can have only dots and numbers, but there can't be two dots in a row. String has to begin with a number and end to a dot

So these are ok
1.1.1.1.1.1.
and
2.452443.1.5.21.5.42131.

but
1..1.
and
1.1.t.
and
.1.1.
and
1.1
are not ok.

How to do this?

Why is 1..1. unacceptable ?

 

[Andrew Poulsom]

Validate how - with a formula or with VBA?

 

[Jon von der Heyden]

Try:

=AND(ISNUMBER(LEFT(A1,1)+0),SUBSTITUTE(A1,"..","")=A1,RIGHT(A1,1)=".",ISNUMBER(SUBSTITUTE(A1,".","")+0))

which will yield TRUE if it is ok, otherwise FALSE

You can use this in Conditional Frmatting or Validation, up to you.

 

[Jaymond Flurrie]

Try:

=AND(ISNUMBER(LEFT(A1,1)+0),SUBSTITUTE(A1,"..","")=A1,RIGHT(A1,1)=".",ISNUMBER(SUBSTITUTE(A1,".","")+0))

which will yield TRUE if it is ok, otherwise FALSE

You can use this in Conditional Frmatting or Validation, up to you.

Looks good. Is there a VBA version for this? I mean, I probably can pretty easily translate this to VBA, but how about that "isnumber"-function?

 

[Jon von der Heyden]

I would probably use a Regex function:

Function IsValid(str As String)
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d\."
        If .test(str) Then
            IsValid = .Replace((str), "") = ""
        End If
End With
End Function

so:

Msgbox IsValid(Range("A1"))

...would yield TRUE if it meets your conditions, otherwise FALSE

 

[Jaymond Flurrie]

I would probably use a Regex function:

Function IsValid(str As String)
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d\."
        If .test(str) Then
            IsValid = .Replace((str), "") = ""
        End If
End With
End Function

so:

Msgbox IsValid(Range("A1"))

...would yield TRUE if it meets your conditions, otherwise FALSE

Thank you. Can you shortly explain how this works, especially what is this .Pattern = "\d\." ?

 

[Jon von der Heyden]

This probably explains it better than I can: http://www.aivosto.com/vbtips/regex.html

/d
is used to match a numeric character

/.
is used to match a dot

Global = True basically checks that the entire string meets this pattern.

Hope this helps.

 

[Jaymond Flurrie]

This probably explains it better than I can: http://www.aivosto.com/vbtips/regex.html

/d
is used to match a numeric character

/.
is used to match a dot

Global = True basically checks that the entire string meets this pattern.

Hope this helps.

Yes, it did help. Thank you!

 

[Jon von der Heyden]

I just spotted a problem with the function. It will not match
24.4.

it expects a single numeric character, and then a dot.

Use this instead:

Function IsValid(str As String)
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d+\."
        If .test(str) Then
            IsValid = .Replace((str), "") = ""
        End If
End With
End Function