By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

How can I parse this string?

P: 7
I have a few hundred records that look like this

mcnn 123030040506
mcas sa 123033
mscc 223449
mcnn sa 1000300300

What I need is the NUMBER part of the string, which, as you can see, the lenght is not always the same, BUT is always separated by an empty space from the rest and always on the right side.

Important: I need to use the query builder.

thanks in advance!!
Jun 28 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,532
InStrRev() will locate the position of the last space in the string and you can then use Right() and Len() to parse out the characters beyond this final space.
Expand|Select|Wrap|Line Numbers
  1. NumberOnly: Right([FieldName],Len([FieldName])-InStrRev([FieldName]," ")+1)
Welcome to Bytes!

Linq ;0)>
Jun 28 '09 #2

Expert 5K+
P: 8,679
Expand|Select|Wrap|Line Numbers
  1. NumberOnly:Mid([Some Field],InstrRev([Some Field], " ")+1)
Jun 29 '09 #3

Expert 2.5K+
P: 3,532
I always forget that Mid() doesn't require that third argument, and that if you omit it it returns everything until the end of the string.

Linq ;0)>
Jun 29 '09 #4

P: 7
great! thanks for the suggestions
Jun 29 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.