Compare ONLY text (no numbers) in 2 strings?

**JP Romano** · 08-30-2011, 01:23 PM

Hello... I have two strings that I'm comparing, but am getting a lot of failures because part of the strings are real time stock prices.
I'd like to compare only the text aspects of the strings, if possible. Anybody know how?

Example of two strings I'm comparing...

This should PASS:
STR1 = QUALCOMM INC (QCOM US) PRICE 50.84 D $ DELAYED
STR2 = QUALCOMM INC (QCOM US) PRICE 50.81 D $ DELAYED

This should FAIL:
STR1 = QUALCOMM INC (QCOM US) PRICE 50.84 D $ DELAYED
STR2 = QUALCOMM INC (QCOM US) PRICE 50.84 Y $ DELAYED

Any idea how I can strip out the numeric values for a comparison?
The text before/after will vary, as will the position within the string, so I can't think of a way to parse more appropriately.

Thank you for any help!

**tlafferty** · 08-30-2011, 01:29 PM

It looks like you're just examining just the ends of the text, so this will do that for you:
[code]=RIGHT(A1,11)=RIGHT(A2,11)[/code
assuming your comparison strings are in cells A1 and A2

**JP Romano** · 08-30-2011, 01:34 PM

Mr. Lafferty, thanks so much for the quick reply! Unfortunately, the position of the price can change, as can the text before or after, so I don't think looking at the right (or left) characters will quite do it. I actually had it set up to concatenate the left 10 and right 10 for each string, then compare those, but ran into problems with about 30% of my tests.
I do appreciate your response though!

**JP Romano** · 08-30-2011, 01:59 PM

I got it!

Please Login or Register  to view this content.

**Andrew-R** · 08-30-2011, 02:16 PM

If you wrote that as a function you could use it on worksheets in other formula, i.e.

Please Login or Register  to view this content.

Edited to add - If your strings are quite long the function would be more efficient if it just looped through the numbers 0-9, replacing each number in turn with a blank. That way it would always take 10 iterations to complete, rather than an iteration for each character of the string.