extract numbers from a string

[stuck]

I need to extract the numbers (4 or 5 of them, it varies) that occur at the end of a string. The string is in cell A1 of an Excel workbook. I can get the string into my Word doc with:

Code: Select all

ActiveDocument.Bookmarks("myString").Range.Text = xlWsh.Range("A1").Text

Any suggestions as to what to do next gratefully received.

Ken

[HansV]

I'd do it like this:

Code: Select all

    Dim strText As String
    Dim i As Long
    strText = xlWsh.Range("A1").Text
    i = 1
    Do While IsNumeric(Right(strText, i)) And i <= Len(strText)
        i = i + 1
    Loop
    ActiveDocument.Bookmarks("myString").Range.Text = Right(strText, i - 1)

[stuck]

In which case why would I do it any other way?

yes, that works,

Thanks,

Ken

[macropod]

Since your original post implies there's always 4 or 5 numbers, I'd start off with 'i = 4' rather than 'i = 1'.

[Rudi]

Hans is looping from the right hand side of the string, so starting at 1 is advisable.

[macropod]

Well, if you start with i = 4, IsNumeric(Right(strText, i)) will start off with the last four characters in the string, rather than having to iteratively and unnecessarily test the last 3 before getting to it. Indeed, you could forego the loop entirely, with:

Code: Select all

With ActiveDocument.Bookmarks("myString").Range
  If IsNumeric(Right(strText, 5)) Then
    .Text = Right(strText, 5)
  Else
    .Text = Right(strText, 4)
  End If
End With

[PJ_in_FL]

Well, if you start with i = 4, IsNumeric(Right(strText, i)) will start off with the last four characters in the string, rather than having to iteratively and unnecessarily test the last 3 before getting to it.

I used to worry about such efficiencies until I tested Excel VBA and found my i3 dual core PC's and laptops were executing tens of millions of lines of VBA code (and gads more machine-level instructions!!) per second!

As long as extraneous screen updating and spread sheet calculations are paused, most any VBA code happens faster than the blink of an eye.

[BenCasey]

Well, if you start with i = 4, IsNumeric(Right(strText, i)) will start off with the last four characters in the string, rather than having to iteratively and unnecessarily test the last 3 before getting to it. Indeed, you could forego the loop entirely, with:

Well, you are right of course, Macropod but unless that assumption (of 4 or 5) is enshrined as a Business Rule and thus sacrosanct to change, I would go with Hans' code because you never know when it might suddenly change. Actually, I might even start at right(1) and keep working left until it runs out of numerics, depending what the client says when I ask him.

[macropod]

The 'rule' is stated in the first sentence of the first post. As such, it is not an assumption. My response concerns that rule, nothing else. As for your last statement, surely that's violating the rule. So what if there's a dozen digits, the specs call for no more than the last 5.

Arguably, if there are less than 4 digits, nothing should be returned, in which case the code should be:

Code: Select all

With ActiveDocument.Bookmarks("myString").Range
  If IsNumeric(Right(strText, 5)) Then
    .Text = Right(strText, 5)
  ElseIf IsNumeric(Right(strText, 4)) Then
    .Text = Right(strText, 4)
  Else
    .Text = ""
  End If
End With

[BenCasey]

The 'rule' is stated in the first sentence of the first post. As such, it is not an assumption. My response concerns that rule, nothing else. As for your last statement, surely that's violating the rule. So what if there's a dozen digits, the specs call for no more than the last 5.

I don't think he stated it was a rule but merely what he wanted to do.
Anyway, as-is your latest code would be good.