For a VLOOKUP try the below where Column D is with the numbers

=VLOOKUP(TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH(":",A1 )+1,99)," ",REPT("
",99)),99)),D:D,1)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

If I understand you correctly you are looking to get the data starting with
SBI. With the below data in cell A1

BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING--

the below formula will extract the number you are looking for...

=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH(":",A1)+1,99), " ",REPT(" ",99)),99))

If this post helps click Yes
---------------
Jacob Skaria


"Manish Gurve" wrote:

Hi

I have a column like this contained in cell a1

BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING--

another column is like this in cell d1

SBINH09222318684

can i put a vlookup between this two. in such a way that formula should find
the second value as lookup_value in cell a1. Can a combination of mid &
vlookup will solve this ? pls help. thanks in advance. As I have to make a
bank reco and the data specified in a1 is in such a way that I can not
seperate that no from the whole list. Thanks in advance