Solved Excel find and replace function

March 12, 2013 at 03:17:35
Specs: Windows 7
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 :)


See More: Excel find and replace function

Report •

#1
March 13, 2013 at 12:51:19
✔ Best Answer
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.


Report •
Related Solutions


Ask Question