Returns a numbers from a string
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.
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
=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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The 6 numbers could be at the end, beginning or middle. I need to pull out the 6 no matter where they are.
ASKER
Rey, yours works well except the case where there are only 6 numbers in the field. Then it returns an error.
ASKER
CS is not always part of it
If always next to "CS"
=MID(A1,FIND("CS",A1)+3,6)
Duncan
=MID(A1,FIND("CS",A1)+3,6)
Duncan
ASKER
I found a work around to handle the records where it just contained 6 numbers, and Rey, your solution handled the rest.
dgravitt,
cool, but next time, it will be helpful if all scenario about the data is available.
cool, but next time, it will be helpful if all scenario about the data is available.
MID(yourtext,LEN(yourtext)
Duncan