Formula to replace cells in range with strings if found

[JP Romano]

Hey all - could use a hand with a formula that I just can't seem to get working. Unfortunately, I cannot use VBA to solve this one.

Spreadsheet (attached) has 2 columns - ShortName and LongName

Need a formula that will find any instance of each short name in the LongName Column, and put that shortname in the adjacent column.

So if Shortname list has "Edgewood" and any cell in the LongName column has "Barnaby Heights, Edgewood, Salvador", the adjacent cell in column C should just read "Edgewood"
Hopefully the attachment will make it clearer than I can describe here.

Thanks for any help!

[Glenn Kennedy]

Can you try to attach the file again.... it dropped off somewhere!

[JP Romano]

Still trying to find the link to add it! Sorry for the delay

[WideBoyDixon]

Assuming we're talking about columns A and B here then this might work in C2:

Please Login or Register  to view this content.

Adjust $A$2:$A$6 to be the full range of ShortName values.

WBD

[Glenn Kennedy]

Oh right!! Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[JP Romano]

WideBoyDixon - that's PERFECT, thank you!

Glenn, sorry, I have no idea why my file won't attach. Not getting any errors when I try, it's just not working.

THANK YOU!