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

pawest

Board Regular
Joined
Jun 27, 2011
Messages
105
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!
 
Last edited:
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top