Right([String,?) Numbers only

Hi All
How do I get (from field "GlasType" using the Query grid) only the right hand side of these strings where I only want them if they are numbers i.e.
From "Pyro 7" I want  7
From "Pyro 15" I want  15
From "GWPP 16.2" I want  16.2
Can I extract the string part as numbers even though they are from a text field?
Thanks
Derek BrownMDAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
It is to prevent a trailing zero to be removed.

/gustav
0
 
peter57rCommented:
"....where I only want them if they are numbers ..."

Does this mean that you have values that do NOT have numbers in the final positions?
0
 
Derek BrownMDAuthor Commented:
Hi Peter

No, All stings end with numbers but I have strings where within say the last 4 characters there are letters as in Pyro 7
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mbizupCommented:
This will do it if there is always a space preceding your number:

mid([YourFieldName],instrrev([YourFieldName]," "))
0
 
mbizupCommented:
And if you need to explicity convert it to a number:

CDbl(mid([YourFieldName],instrrev([YourFieldName]," ")) )
0
 
Derek BrownMDAuthor Commented:
Hi that's great

Unfortunately there often is no space between text and number. Is there any function that would simply say find first number? I have spent a couple of hours with it but can't see anything!
0
 
Derek BrownMDAuthor Commented:
Hi

Isn't there a split function. (Is function the correct term?)
0
 
mbizupCommented:
There is a split function which returns an array of values from your string that were seperated by some delimiter.   However if you just need a number from the right hand side, split is overkill and overly complicated.
0
 
Derek BrownMDAuthor Commented:
Hi I've just looked up a Val Function it supposed to get the number value from a string but in the  query grid it doesn't. Any ideas?

Derek
0
 
Derek BrownMDAuthor Commented:
Hi When I type  Right([GlassType],3) am I getting the last 3 numbers or the 3rd from the right?
0
 
Derek BrownMDAuthor Commented:
Hi

If I could get just the 4th number from the right I could ask if fourth number from right is =>0 then
Do something, Do something else
0
 
Gustav BrockCIOCommented:
You can use this expression:

GlasTypeNo: StrReverse(Mid(Str(Val(StrReverse([GlasType] & "9"))),3))

/gustav
0
 
Derek BrownMDAuthor Commented:
Hi
that looks promissing what does the "9" mean?
0
 
Derek BrownMDAuthor Commented:
Tried it, works.

Brilliant.
Thank you
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.