Text formatting problem? Inconsistent returns from Lookup formula.

[HughT]

I have a large csv file imported to a worksheet. From this I extract data to a separate Analysis worksheet to create the columns I need by using '=' to look up the data from the csv worksheet. So for example W1 in the csv worksheet = C1 in the analysis worksheet etc, which contains the random text string from the csv worksheet. I have created a column in the analysis worksheet (AD) which contains the text strings I need to search for, which returns text (an instruction) contained in a third column (AE).

Using a lookup formula from Eric W https://www.mrexcel.com/board/members/eric-w.338656/ (to whom many thanks) I am able to interrogate the random text using the formula

=LOOKUP(2,1/SEARCH($AD$3:$AD$45,"*"&C3&"*"),$AE$3:$AE$45)

Where column AD contains the search string to be searched for, column C is the random text string containing the text in column AD to be searched, and column AE contains the text to be returned if column C contains the text in column AD. This formula is in column D and is copied down the whole of column D. This works perfectly well for two other pairs of search strings and responses in other columns, so the formula works. However for the third pair it returns inconsistent responses. This seems to be based on the formatting of the cells in column C, but this is inconsistent.

It works for one search string (out of 43 in column AD) wherever that search string occurs in the text to be searched in a cell in column C (ie by itself, in the middle of the text string, or at the end of it). The rest return #N/A. But if I copy and paste any search string text from column AD into text in column C (the text to be searched) it returns the correct response. The same occurs if I type the search string into column C manually. So the formula can find the correct text string and return the correct response if the input is manual. I have tested this by replacing the text to be searched (column C) with all the search text (column AD) and in every instance the formula returns the correct response from column AE. So this does not appear to be a formula error.

I have tried formatting the columns as 'General' and 'Text' and this makes no difference. I have checked that the search string column (AD) and the response column (AE) are the same length (43 cells). The formula is consistent throughout. But the formula only returns the correct solution from column AE if it is either one particular text string in column AD (which does not seem to have any unique characteristics, it's just a text string), or the search string text is copied and pasted into column C, although the identical text is already in column C.

Any ideas anyone?

Many thanks

HT

 

[jasonb75]

However for the third pair it returns inconsistent responses. This seems to be based on the formatting of the cells in column C, but this is inconsistent.

That doesn't give us much to go on, an XL2BB sample is worth a thousand pictures

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[HughT]

Jason, many thanks for your response, problem is that the data is actually sensitive customer data and I would have to create a large range of dummy data to do it.

However, I tried overtyping manually the first of the search strings with exactly the same text to see what would happen, and it worked! So I have now done that for all of them. The text strings were copied and pasted from a Word document into Excel and however I tried to format them (General, Text etc) for some reason Excel wouldn't 'see' them. Mystery of life!

Thanks for your time anyway!

HT

 

[jasonb75]

however I tried to format them (General, Text etc) for some reason Excel wouldn't 'see' them.

You can only format valid numbers, if a number has been imported as text then any format changes are ignored until the actual cell content is 'fixed'.
That said, your post implies that the cells contain text anyway, which is always text regardless of cell format.

Try pressing f2 then enter without retyping, does that fix it? If so then a simple vba procedure to apply a fix to the whole sheet should be possible.

If that doesn't work, then the next option depends how the data is being copied in (word to excel is not something that I do so not sure of exact behaviour), there could be some hidden characters being included in the copy / paste action. This is quite common with web data, perhaps word is similar (break characters to format word tables), or there could even be additional spaces that the start / end of the text.

These can be identified by clicking into the cell as if you were going to edit (not retype) the content, then using the left and right cursor keys to move through the characters in the cell.

Spaces at the start will be easy to identify by moving the cursor all the way to the left, pressing it again for certainty and looking for a gap between the cursor and first character (same can be done by moving to the right and checking last character).

To find hidden (zero width) characters, use the same method, start with the cursor as far left in the cell as possible. Tap the left cursor a few times to make sure if is all the way over, then start tapping right. The cursor should move over one character with each press, if it doesn't then it means that you have found a 0 width character in the cell.

 

[HughT]

Thank you very much! Unfortunately I have to deal with tables people create in Word (such people do exist) and import them into Excel.

Many thanks for your time and help.