Parsing Values out of text strings 5

[SVO4Turbo]

I'm trying to figure out a way to parse a value out of a text string. The text strings are variable length, so the MID, Left, Right functions will not work.

Is there a way to have it find a character in the string and take the numeric values to the right out and place them in a new field?

For example:

! 1323 ABC#2146382227 OTHR#5084957 DISB NP PRPS$27!

In this example, I simply need to parse out the very last number "27" out of the string. In all cases, the "$" preceeds the value I want.

Any suggestions?

 

[MakeItSo]

Yepp! and the right-function does work...;-)

Dim myVal,x
x=instr(1,yourstring,"$&quot
myVal=right(yourstring,len(yourstring)-x)<--or is it -x-1? try...

If the $ can occur several times and you have to find the last one, just put the x=instr... into a loop that processes the rest of the string to the right of the $...


MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[SVO4Turbo]

So is there any way to do it without using VB? I can do that, I just was hoping I could do it in the query. Also, I should mention that the &quot;27&quot; in that field, is variable length. Could be &quot;8&quot; in one and &quot;300&quot; in another.

 

[jebry]

Hi!

As long as it is at the end of the string you can do this:

MyNewField: Mid([YourTable]![YourField], InStr([YourTable]![YourField], &quot;$&quot + 1)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[MakeItSo]

Turbo. You can combine mine and Jeff's approach.
Add a self-defined field in your query:

myVal: right([yourstring],len([yourstring])-instr(1,[yourstring],&quot;$&quot
)

The right-function will work with every length of the value, no matter if 3 or 3000.
Jeff's mid-function would work, but only with fixed number of chars at the end.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jebry]

Hi MakeItSo!

Actually the Mid function will work with any number at the end. As long as you provide the Mid function with only one number then it will return from that position to the end of the string. so I provided it with the position after the $ which is why I added the 1 to the InStr value. I am still concerned as to the position of the $999, will it actually and always be at the end of the string? If not, then neither method will work and we will need to go to VBA to do the parseing. Unless you always know what will follow the number.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[SVO4Turbo]

Very cool. I haven't use the &quot;Instr&quot; function before. I couldn't find anything in help showing me an example of how to use it. One more question. Using the code above, I end up with &quot;27!&quot;. How can I get rid of the &quot;!&quot; at the end. 90% of the records have this. Because it's text, I'm guessing there's no way to define the numeric value vs. not.

Suggestions?

 

[jebry]

Hi!

You can try this, though it may be too complex for the Jet Engine:

MyNewField: Mid([YourTable]![YourField], InStr([YourTable]![YourField], &quot;$&quot + 1, Len([YourTable]![YourField]) - 1)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[MakeItSo]

Yepp.
if the ! is always at the end:
myVal2: If(Instr(1,myVal,&quot;!&quot>0,left(myVal,Instr(1,myVal,&quot;!&quot-1,myVal)

Now myval2 always contains the correct value.

MakeItSo

 

[jebry]

Hi!

You did say 90% of the time there is a !. To take care of the other 10% as well as the 90% try this:

MyNewField: IIf(Right([YourTable]![YourField], 1) = &quot;!&quot;, Mid([YourTable]![YourField], InStr([YourTable]![YourField], &quot;$&quot + 1, Len([YourTable]![YourField]) - 1), Mid([YourTable]![YourField], InStr([YourTable]![YourField], &quot;$&quot + 1))

hth


Jeff Bridgham
bridgham@purdue.edu

 

[SVO4Turbo]

Seriously, both of you guys rock!

This works very nice. Thanks for your help!

 

[MakeItSo]

Thanks Turbo. And thanks for the good coop., Jeff.
You're right, mid() works perfectly, too.
And Geeee - you didn't even have a typo in your monster-IIF-mid-instr-function! WOW!

Andy

 

[jebry]

Hi Andy!

The lack of a typo is called copy and paste! Without which my life would be miserable!!!





Jeff Bridgham
bridgham@purdue.edu

 

[CajunCenturion]

It seems that this issue has been taken care of, with possibly one exception, and that being if there is more than one $ in the string. Even if that is the case, it would not be necessary to loop through the string, or use some VBA code to do the parsing.

For future reference, check out the InStrRev function.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MakeItSo]

That's why you're tip master this week Cajun.
That is of course the easiest and safest way.

Congrats,
Makey

 

[TonyJollans]

Before this is put to rest, a couple of points.

InStrRev is a VBA function which is not (in A2K anyway) available for use directly in Queries, so if you did have more than one &quot;$&quot; you would probably require a custom VBA function, though it wouldn't need a loop.

Secondly rather than checking for the &quot;!&quot;, have you considered using the Val function which will take as much as makes sense as a number from the left of a string. In this case it would drop the &quot;!&quot; if it existed and do nothing if it didn't ..

Val(Mid([YourTable]![YourField], InStr([YourTable]![YourField], &quot;$&quot + 1))

Enjoy,
Tony

 

[CajunCenturion]

Good point Tony on both the InStrRev and Val functions.

In any event, if it turned out that a custom VBA function was required, then I would implement a Regular Expressions based solution.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein