Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to vlookup keyword within text string


Hello everyone,

I had a difficulties to form a formula in excel. 

I have a set of bank information data which then I will filter by a transaction (ZBA/XBS) that is in column V . From that, I need to search in column X (payment details) a few bank account numbers that lies within the text strings.  eg. bank account number highlighted in yellow. I would like my formula to be in column HK so that I can easily filter. 

I have a few bank account numbers that I need to lookup from column X.  I got attached picture for you to see.

Sorry for my poor explanation. Hopefully someone can understand and help me. :)



Selected Answer

This is the formula which does the job.

[Row 3] =TRIM(MID(TRIM(MID($D3,FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),3))-LEN("DATE;"))),4,100))

At the heart of the above formula is this function. It substitutes 3rd occurrence of a semicolon with CHR(160) - which doesn't normally occur in text - and returns the position of that character. The critical argument is the 3 at the very end which determines that it is the position of the 3rd occurrence that you want.


This works because the text you have seems to be computer generated, separating lines by semicolons. The account number you want is in the 4th line. Logically, the 4th line is between the 3rd and 4th occurrence of a semicolon in the text.

Further with this logic, the line you want is the MID(String) starting at the 3rd occurrence of ";" +1 (so as to exclude the semicolon itself) and continuing for the distance between the 3rd and 4th occurrence of ";". That line would include the word "DATE followed by a semicolon. So the formula might just specify -5 for the length of the desired string. To make it more transparent I defined this number as LEN("DATE;") which always evaluates to 5.

The result of that part of the formula is something like this: " 436 0158928604 ". Observe the leading and trailing blanks. The TRIM() function removes them. That is the inside TRIM which leaves a string like this: "436 0158928604" from which the formula then removes the first 4 characters and trims the result once again. The 100 toward the end of the formula specifies the maximum length of output. Actually, there are only 10 or 11 characters in the string remaining at that point.

The final step (removing 4 characters) is illogical to some extent because if the branch code could ever have more or less than 3 characters followed by a blank the returned account number would be wrong. The formula should be tweaked to accommodate that possibility. However, based on this reliance on format, one might perhaps also assume that the account number always has 10 digits. In that case the entire formula might be simplified as shown below.


Edit 06 Sep 2018   ==================================

I don't like yesterday's solution anymmore because it presumes that the account number will alsoways be in the 3rd line which will always be followed by the "DATE". The formula below takes a more direct approach. It looks for the line identified as "YOUR REF NO;". In its simplest form, given below, the account number will be 17 characters to the right of that point and 10 characters long.

=MID($D3,FIND("YOUR REF NO;",$D3)+17,10)

If "YOUR REF NO;" (case sensitive) isn't found an error will occur. The expanded formula below gives notice.

=IFERROR(MID($D3,FIND("YOUR REF NO;",$D3)+17,10),"Ref not found")

Please take all of the above as a starting point for your considerations. The solution you finally deploy must rely on the uniformity of your received data. The above merely shows how the section that contains the account number can be identified. Once that is accomplished there are many ways to extract the account number from the section. Please don't hesitate to ask once you know more facts.



Hi, thanks for your answer.
I have inserted the workbook. appreciate your help.
syah1606 (rep: 2) Sep 4, '18 at 11:27 pm
Hi thanks alot.
I wonder if we can use IF function?
syah1606 (rep: 2) Sep 13, '18 at 2:23 am
As a matter of principle, if you can get a result by reading data, that solution is better than one which extracts it with an IF function. I think this principle applies here. However, if you would like to construct a solution using IF just for argument's sake, how would you formulate the condition in plain language? What is the condition (or conditions) that, when answered yes or no, provides the account number you wish to extract?
Variatus (rep: 4889) Sep 13, '18 at 5:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login