Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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
0
DatabaseDek
Asked:
DatabaseDek
  • 8
  • 3
  • 3
  • +1
1 Solution
 
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
 
DatabaseDekAuthor 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
 
mbizupCommented:
This will do it if there is always a space preceding your number:

mid([YourFieldName],instrrev([YourFieldName]," "))
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
mbizupCommented:
And if you need to explicity convert it to a number:

CDbl(mid([YourFieldName],instrrev([YourFieldName]," ")) )
0
 
DatabaseDekAuthor 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
 
DatabaseDekAuthor 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
 
DatabaseDekAuthor 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
 
DatabaseDekAuthor Commented:
Hi When I type  Right([GlassType],3) am I getting the last 3 numbers or the 3rd from the right?
0
 
DatabaseDekAuthor 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
 
DatabaseDekAuthor Commented:
Hi
that looks promissing what does the "9" mean?
0
 
Gustav BrockCIOCommented:
It is to prevent a trailing zero to be removed.

/gustav
0
 
DatabaseDekAuthor Commented:
Tried it, works.

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

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now