Issue With Empty Text String Beig

[denzo36]

Hi folks,

I have a list of messy names that I need to compare to another messy list.

I've used a combo of TRIM and multiple nested SUBSTITUTE formulas to remove things like commas, spaces, slashes, full stops etc. See formula in cell Q2 below

I thought that would remove all unwanted text and spaces but when I use the SEARCH function to find an empty string, it's returning a 1 - I would have expected a 0 for this

Any ideas why this is?

 

[Joe4]

That will return a 1 for ANY non-empty string, regardless of how the string is created.
You are telling it to find an empty string within a non-empty string. I am not sure that makes much sense.

If you want to test to see if Q2 is empty, better to use something like:

=LEN(Q2)

or

=IF(Q2="",0,1)

 

[denzo36]

Thanks Joe,

The problem I'm having is I'm comparing 2 fields that can be similar but not quite the same and I'm trying to be as accurate as I can.

I've found the best approach is the use the IF, ISNUMBER and SEARCH as per below - but for cell T2 below I was hoping to return a FALSE as I'm trying to say, if the left 10 characters in Column S are found in Column Q then TRUE - when Column S is blank or an empty string I would have thought using the SEARCH for the empty string into a string that isn't empty would not return a 1 hence provide a FALSE to the IF.

Not sure there is a way around this then

 

[Fluff]

How about

=IF(S3="","",ISNUMBER(SEARCH(LEFT(S3,10),Q3)))

 

[denzo36]

How about

=IF(S3="","",ISNUMBER(SEARCH(LEFT(S3,10),Q3)))

Thanks Fluff, I think some combo of IF, OR and what I already have will do it for me actually

 

[Fluff]

Does that formula not work?

 

[denzo36]

Does that formula not work?

It works in the Excel sense but wasn't doing what I wanted it to do - I have it working now for my needs

Thanks again

 

[Fluff]

Glad you sorted it & thanks for the feedback.