Solved

Returns a numbers from a string

Posted on 2014-07-30
9
161 Views
Last Modified: 2014-07-30
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
Comment
Question by:dgravitt
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:duncanb7
Comment Utility
Is it what your want ?

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

Duncan
0
 
LVL 19

Expert Comment

by:Iammontoya
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
this should do it

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

Author Comment

by:dgravitt
Comment Utility
The 6 numbers could be at the end, beginning or middle. I need to pull out the 6 no matter where they are.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

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

Author Comment

by:dgravitt
Comment Utility
CS is not always part of it
0
 
LVL 13

Expert Comment

by:duncanb7
Comment Utility
If always next to "CS"
=MID(A1,FIND("CS",A1)+3,6)

Duncan
0
 

Author Closing Comment

by:dgravitt
Comment Utility
I found a work around to handle the records where it just contained 6 numbers, and Rey, your solution handled the rest.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
dgravitt,
cool, but next time, it will be helpful if all scenario about the data is available.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now