Need formula for search the text in cell and return the given result using mid function
- Thread starter sguna1982
- Start date
NARAYANK991
Excel Ninja
Hi Guna ,
Try this :
=MID(A2,FIND(" for ",A2)+5,FIND(" on ",A2)-FIND(" for ",A2)-5)
Copy this down.
Narayan
Try this :
=MID(A2,FIND(" for ",A2)+5,FIND(" on ",A2)-FIND(" for ",A2)-5)
Copy this down.
Narayan
NARAYANK991
Excel Ninja
Hi Guna ,
Your text is free-form text , not in any standardized format , unless we take the following as the possible formats :
Taking care of all of these using a formula is going to be cumbersome ; using VBA will make it very easy.
Narayan
Your text is free-form text , not in any standardized format , unless we take the following as the possible formats :
Code:
Your Loan Account Number is BLRHL14000934.
EMI of Rs. 100 for Loan Number PUNHL14000834 is due on 07-06-2014.
Your Loan Account Number AHMHL13000174 could not be processed
Your Loan bearing AHMHL13000056 has been disbursed.
Your Loan bearing Loan Account Number AHMHL14000360 has been sanctioned.Narayan
Somendra Misra
Excel Ninja
Hi sguna,
The type of data your uploaded first and now is very different. Such formulas work for some specific cases based on your data and are not in general.
Looking at your recent data, I can suggest you to follow below steps:
1. Covert your data from Text to column from data tab , with deliminater as Space.
2. Apply INDEX-MATCH formula to extract account number as in attached file.(Note I had assumed two things in formula. 1) Length of account number is same = 13 and every account number have HL word in between) if that is the case in all your data you can go with this solution.
Note this formula will fail if you had any special character just before or after Account number in your data. So take care of it. Your account number must be followed and preceded by a space.
Regards,
The type of data your uploaded first and now is very different. Such formulas work for some specific cases based on your data and are not in general.
Looking at your recent data, I can suggest you to follow below steps:
1. Covert your data from Text to column from data tab , with deliminater as Space.
2. Apply INDEX-MATCH formula to extract account number as in attached file.(Note I had assumed two things in formula. 1) Length of account number is same = 13 and every account number have HL word in between) if that is the case in all your data you can go with this solution.
Note this formula will fail if you had any special character just before or after Account number in your data. So take care of it. Your account number must be followed and preceded by a space.
Regards,
NARAYANK991
Excel Ninja
Hi Misra ,
In the first line I have posted , the account number is followed by a period.
Narayan
In the first line I have posted , the account number is followed by a period.
Narayan
Somendra Misra
Excel Ninja
Hi Narayan Sir,
I noticed that during working on it and added the same in my note.
Regards,
I noticed that during working on it and added the same in my note.
Regards,