Parsing variables XXX12 and XXX2 to compare XXX, must be excel function

[pawest]

Hello VBA world... well this question is for Excel Function world:
I am trying to parse a string and return just the text within a string. The text strings are the same but the ending number variations differ. For example, I have two comparable variables "XXX12" and "XXX2" and I'm trying to parse out the XXX.

Factors making this difficult:

• The beginning text could range from X to XXXXX
• The ending numbers could be any single or double digit combination on the first variable and any single digit on the second variable, so the first variable could be "XX11" and the second variable could be "XX1" or the first "XXXX3" and the second "XXXX3"... the integers at the end of the string will rarely ever follow follow the same pattern
  • Therefore, a simple =FIND() won't work because of the variability of numbers
  • I tried a FIND("XX11",OR(0,1,2,3,4,5,6,7,8,9)) but an OR nested in FIND doesn't work
• This MUST be some combination of excel formulas and NOT a hardcode derived from VBA

I've tried functions such as LEFT, RIGHT, LEN, ISNONTEXT, ISNUMBER, FIND, CHAR, LEN, OR, SUBSTITUTE, etc... and I'm not having much luck. Please let me know if anyone has encountered this before and if you could help provide a solution.

Thanks!

 

[pgc01]

Hi

I'm glad it helped.

I used 2 features of LookUp:

- If you use LookUp() with a lookup value bigger than any of the values of the lookup array, the function matches the last value

- LookUp() ignores error values


In this case 1/Search() will always be either an error value or a value less than 2. This means that the function returns the value corresponding to the last match, one of the values in the array in curly brackets that you supply.

In case no match is found, LookUp() will return an error value triggering the other return value in IfError().

Hope it's clear.