INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Locate text based on string criteria

Locate text based on string criteria

(OP)
I have a set of data that I need to attach a revision number to. The revision number is located in a log message that is included in the set of data but I'm having difficulty isolating the number so I can put it into the revision number field.

Here's some examples of log messages:

Jane Doe submitted schedule revision number 10.
Marty McFly gave copy traffic confirmation to revision number -1.
Peter Venkman gave managerial approval to revision number 24.

Now the best way that I can think of to do this would be to run a query to pull up the log message for each record and whenever it comes across the string "revision number" in the message, the number that follows that string will be the value inputed into the revision number field.

I figure that once I can identify the string "revision number" then I would just use the Isnumeric() function to find the number that I need whether it's 1 or 100 but I can't seem to find anything that works for me.

So any suggestions you guys might have will be greatly appreciated.

Thanks,

Travis
Charter Media

RE: Locate text based on string criteria

One way...:

CODE

Dim str As String

str = "Jane Doe submitted schedule revision number 10."
'str = "Marty McFly gave copy traffic confirmation to revision number -1."
'str = "Peter Venkman gave managerial approval to revision number 24."

MsgBox "Rev No is: " & Val(Mid(str, InStr(str, "revision number") + Len("revision number") + 1)) 

You may want to use ABS() function for the value of -1 (second line of your text)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Locate text based on string criteria

(OP)
Here's the formula that I used:

Abs(Mid([logmessage],InStr([logmessage],"revision number")+Len("revision number")+1))

And it works really well except when the log message looks like this:

"Doc Brown exported revision number 0: ."

From what I can tell if there is more than just a period after the revision number the result comes out as #Error.

What can I do to resolve those errors?

Travis
Charter Media

RE: Locate text based on string criteria

There is not much you can do.
Validating text and retrieving valid data out of the text is very hard, After you fix one problem, you will find another 12 that you did not account for. What if you find the text: “Doc Brown exported revision number 15 and is happy now.", or "Doc Brown exported rev no 15 and now is sad."? And 100 other possibilities.

You may ‘scan’ the text character by character and detect the number(s), but that’s not 100% proof method, either.

Sometimes the fastest way – believe it or not – is to do it ‘by-hand’, record by record. But then, from that time on, make your users enter this data into separate field in your DB.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Locate text based on string criteria

(OP)
Andrzejek,
I see what you're saying, and I agree with you. Unfortunately this data is part of a report we receive from a 3rd party system and there's no user input in this database, it's just a processor for the data we receive so we can report on it.

And doing it by hand isn't really effective either since there are over 1.5 million records.

Travis
Charter Media

RE: Locate text based on string criteria

In this case I would ‘squeeze’ out of whoever provides you the data a rule that drives of how the revision number is presented. You may get lucky and find out that “the revision number is the last number in the field” in which case you start from the end, evaluate each character, and as soon as you find a number you would keep going backwards and look for the space, which would be (hopefully) the beginning of the number.

Just a guess here...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Locate text based on string criteria

(OP)
That's what I've been trying to do but I haven't had any luck yet.

In the meantime, I've modified the code you originally sent me and so far it looks like this:

Abs(Val(Mid([logmessage],InStr([logmessage],"revision number")+Len("revision number")+1)))

I've been spot checking the results and I haven't found any issues as of yet.

Travis
Charter Media

RE: Locate text based on string criteria

Public Function RevNum(strLog As String) As String
Dim myMatch As Object
With CreateObject("vbscript.regexp")
.Pattern = "revision number.+?(\d+)"
Set myMatch = .Execute(strLog)
If myMatch.Count > 0 Then RevNum = myMatch(0).SubMatches(0)
End With
End Function

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close