Hi there, I have 2 columns and I need to find a function to look for for text from eg. cell A1 in whole column B and return with the text after comma from column B eg:

A1 "Smith, Bob", somewhere in B:B we have "Smith, Bob Adam" so in col C I need "Smith, Bob Adam" ... if that make sense?

I would really appriciate some help :)

Your question is just a bit confusing. First, your Subject Line says "

find and replace" but I don't see where anything is being "replaced". It seems that you are simply trying to "find" a matching value and return it.Second, you said "

return with the text after comma from column B" yet your example seems to indicate that your want the entire contents of the cell returned, not just the text after the comma:so in col C I need "Smith, Bob Adam"So, assuming that you want to return the entire cell contents, I would try the following procedure. The Columns you use won't matter, as long as you follow the rules for VLOOKUP.

In Column C, enter this formula and drag it down:

=LEFT(B1,FIND(" ",B1,FIND(" ",B1)+1)-1)

This will strip off the e.g. " Adam" (space + Adam) from the values in Column B leaving e.g. Smith, Bob

In column D, enter this formula and drag it down:

=MID(B1,FIND(" ",B1,FIND(" ",B1)+1),LEN(B1))

This will place e.g. " Adam" (space + Adam) in Column D

In Column E, enter this formula:

=VLOOKUP(A1,$C$1:$D$6,1,0) & VLOOKUP(A1,$C$1:$D$6,2,0)

This will lookup the value in A1 in Column C and in Column D and concatenate them back together, returning Cx & Dx, e.g. Smith, Bob Adam

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll