If a string ends in numerics and not characters, then

[CJ]

How would I go about isolating a string that begins with a certain text, but
ends in numbers? i.e.,

C.A. No. 12345-6789

It will always start with C.A. No. and should end with 3 text characters,
not numbers. C.A. No. 12345-6789-XXX (the x's are variable letters but
always 3 characters).

I need to alert the user that it does not end in 3 characters, and in a best
case scenario, give them the opportunity to insert the characters. Is this
too tall an order?

Thanks.

 

[Amish]

There are probably many ways, but here's two:
Count the characters [with len(x)] - this is probably the easiest IF
the spaces and hyphens are always in there,
Look at the last three [with right(x,3)] and test their values [chr
(48) = the zero digit, chr(57) = nine]

You can also count the hyphens and look for the test after the second
one (again assuming that they are in there).

 

[David]

Search starts at the cursor. Stops with the items selected when the
last three are numbers.

Skips this one.
C.A. No. 12345-6789-ABC

Stops at this one.
C.A. No. 12345-6789-123


Sub EndingNumbers()

Dim aDoc As Document
Dim Rng As Range
Dim Rng2 As Range

Set aDoc = ActiveDocument
'Set search start point to cursor
Set Rng = aDoc.Range(Selection.Start, aDoc.Range.End)
Set Rng2 = Rng.Duplicate

Do
With Rng2.Find
.ClearFormatting
.Text = "C.A. No."
.Forward = True
.Wrap = wdFindStop
.Execute
End With


If Rng2.Find.Found Then
With Rng2
.MoveEndUntil vbCr
.MoveStartUntil "-"
.MoveStart wdCharacter
.MoveStartUntil "-"
.MoveStart wdCharacter

If IsNumeric(Trim(Rng2)) Then
Rng2.Select
Exit Do
End If
End With
End If
Rng2.MoveStart wdWord
Rng2.End = Rng.End
Loop Until Not Rng2.Find.Found

End Sub

 

[Greg Maxey]

How would I go about isolating a string that begins with a certain text, but
ends in numbers?  i.e.,

C.A. No. 12345-6789

It will always start with C.A. No.  and should end with 3 text characters,
not numbers.    C.A. No. 12345-6789-XXX (the x's are variable lettersbut
always 3 characters).

I need to alert the user that it does not end in 3 characters, and in a best
case scenario, give them the opportunity to insert the characters.  Is this
too tall an order?

Thanks.

If your pattern is consistent number group - number group - alpha
group then you could use something like this:


Sub EndingNumbers()
Dim oRng As Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Text = "C.A. No. [0-9]@-[0-9]@-[0-9]{3}"
.MatchWildcards = True
.Forward = True
.Wrap = wdFindStop
While .Execute
oRng.Collapse wdCollapseEnd
oRng.MoveStart wdCharacter, -3
oRng.Select
oRng.Text = InputBox("The data is invalid. Please enter alpha
characters", "Invalid", "ABC")
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

 

[Dorak]

David's second suggestion is spot on. Thanks!