### in cell replacing text - vlookup not working

[aurness]

Hello -

I have several rows of data, all text, that I'm looking for specific word(s) using VLOOKUP. The cells the text is in is currently formatted as "general", and I noticed that the VLOOKUP was not picking up all the words so I converted the cell format to "text" where the cells where the text was not being picked up - converted to a series of ####.

My question, what can I do to those cells with the ##'s in order for the VLOOKUP (or other formula) to recognize the words?

Thanks

[FDibbins]

Hi and welcome to the forum

without seeing any of the data you are working with, these are guesses, but 2 things to consider...
1. the #### may be because your column isnt wide enough?
2. vlookup generally only returns the very 1st instance of what you are searching for. If there are repeats after that, they will not be found

[aurness]

Ok, here are some additional details. The amount of characters in the cell is 283, 340 with spaces and when the cell is formatted as "general" it's readable, but in "text" it's ##'s.

Here is one example of the actual text:
I wanted to compliment Victoria Munoz in the handbags dept. in the Santa Anita Nordstrom. Victoria was extremely patient and helpful with my purchase. She kindly and patiently helped me find and order bags from the Internet as well as inquired them from numerous stores. I am very thankful and happy with her high level of customer service.

And here is a sample of the actual formula I'm using, which works on most cells - just not the one's that are showing as ##'s.
=IF(OR(ISNUMBER(MATCH("*"&Lists!$A$2:$A$5&"*",B2,0))),"accuracy","")

This is just a sample, not the exact formula, but the fundamental is the same, and there is a "LIST" tab on the file.

Any thoughts would be awesome

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[aurness]

Here you go - I think this will show the situation.

[Pepe Le Mokko]

Format column B as general then enter

Please Login or Register  to view this content.

in C2
Pull down as needed

[aurness]

This is perfect, thank you! It worked!!