# Formula to Extract Numbers from Text Cell

1. ## Formula to Extract Numbers from Text Cell

Hello,

I was wondering if someone could help me. I need a formula to extract numbers. The problem is that what I need are loan numbers and some numbers within the cell is not part of the loan number. For instance:

DATA (Column A)
B012.T01234TOPSHEET
B1235678901234T22222

RESULT (Column B)
BLANK (No Loan Number to extract)
1234567890
5678901234

Is there a formula than can do this for me?

The amount of data changes every month. Sometimes there is one day of activity and sometimes there are 5 to 10 days worth of activity. I need to do the same formula for multiple spreadsheet and would also like to know if I can create a macro for this as well.

Thank you!
~MHamid  Register To Reply

2. ## Re: Formula to Extract Numbers from Text Cell

I'm not entirely clear about the criteria. It appears that you want to find the first "T" and extract the 10-digit value that precedes it.
If that's true, try this regular formula:
B1: =IF(COUNT(-MID(A1,FIND("T",A1)-10,10)),MID(A1,FIND("T",A1)-10,10),"")

Is that something you can work with?  Register To Reply

3. ## Re: Formula to Extract Numbers from Text Cell

Maybe this...

=MID(A2,FIND("T",A2)-10,10)  Register To Reply

4. ## Re: Formula to Extract Numbers from Text Cell

AWESOME!! This works perferctly!! I shoudl have just asked sooner instead of spending hours trying to figure it out.   Register To Reply

5. ## Re: Formula to Extract Numbers from Text Cell

Teethless mama, yours worked great as well!! Thanks for your help.   Register To Reply

6. ## Re: Formula to Extract Numbers from Text Cell Originally Posted by MHamid AWESOME!! This works perferctly!! I shoudl have just asked sooner instead of spending hours trying to figure it out. Great!
Don't forget to mark this post SOLVED so others will know. (Select the first post...then: Thread_tools.Mark_this_thread_solved.)  Register To Reply