Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Returns a numbers from a string

Posted on 2014-07-30
Medium Priority
168 Views
I have a string "115 CS 575840" that I need to return the 6 numbers that are together. So, from this string I need to return 575840.
0
Question by:dgravitt
• 4
• 2
• 2
• +1

LVL 13

Expert Comment

ID: 40229351
Is it what your want ?

MID(yourtext,LEN(yourtext)-5,6)

Duncan
0

LVL 19

Expert Comment

ID: 40229359
if your last numbers are always six digits you can try this:

=NUMBERVALUE(RIGHT(B3,6))

this takes the text in column B3 and takes the 6 digits from the end, and converts them to a number value
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 40229403
this should do it

trim(mid(yourString,instrrev(yourString," ")))
0

Author Comment

ID: 40229417
The 6 numbers could be at the end, beginning or middle. I need to pull out the 6 no matter where they are.
0

Author Comment

ID: 40229431
Rey, yours works well except the case where there are only 6 numbers in the field. Then it returns an error.
0

Author Comment

ID: 40229462
CS is not always part of it
0

LVL 13

Expert Comment

ID: 40229475
If always next to "CS"
=MID(A1,FIND("CS",A1)+3,6)

Duncan
0

Author Closing Comment

ID: 40229523
I found a work around to handle the records where it just contained 6 numbers, and Rey, your solution handled the rest.
0

LVL 120

Expert Comment

ID: 40229534
dgravitt,
cool, but next time, it will be helpful if all scenario about the data is available.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
###### Suggested Courses
Course of the Month8 days, 8 hours left to enroll