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!

 

[texasalynn]

you need the reference to be the same, so your example needs to be x13

=LEFT(X13,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"_","@"},T13&1234567890_@))-1)

 

[pgc01]

Hi

You are missing the double quotes in the string and you should use the same address:

=LEFT(X13,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"_","@"},X13&"1234567890_@"))-1)

 

[Rick Rothstein]

This formula will pull out the one or two digit number at the end if there is one and return the empty string ("") if not (that could be changed to return 0 instead if desired)...

=IF(ISNUMBER(-RIGHT(A2,2)),1*RIGHT(A2,2),IF(ISNUMBER(-RIGHT(A2)),1*RIGHT(A2),""))

 

[pawest]

Looks like I got it!

=LEFT(X13,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"_","@"},X13&1234567890&"_"&"@"))-1)

Thanks everyone for all your help! This helps tremendously!

 

[pawest]

Thanks, again, to everyone who helped. These solutions are great! I thought I knew a lot about excel until I saw this function! I didn't know much about this one! Taught me something new!

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

Notice that you can use a string with all the characters, like I posted

Instead of

X13&1234567890&"_"&"@"

you can use

X13&"1234567890_@"

 

[pawest]

Relating to this conversation, I encountered an issue with the brackets finding the correct information. Here's a similar find/search function that I'm using:

=IFERROR(IF(SEARCH({"FEE","REBATE"},HK2),{"FEE","REBATE"},),(TRIM(RIGHT(HK2,LEN(HK2)-7))))

This works on "Fees" but not work on "rebates".

For example...
When using the above function on this "FEE DFS REBATE: 12D* DOIUF" the function correctly returns "FEE".
When using the above function on this "ACD CH REBATE July 2012 " the function doesn't recognize REBATE, recognizes it as false, uses this part of the function "(TRIM(RIGHT(HK2,LEN(HK2)-7)))" and returns "REBATE July 2012", rather than just returning "REBATE" like I want.

This has something to do with the order of the {"FEE","REBATE"} word order because when I switch both brackets to {"REBATE","FEE"}, the function works.

Any suggestions?

 

[pawest]

Still trying to solve the above issue... does anyone have any links or insight on how to properly use the brackets within functions?

Thanks!

 

[pgc01]

Hi

Try:

=IFERROR(LOOKUP(2,1/SEARCH({"FEE","REBATE"},HK2),{"FEE","REBATE"}),TRIM(RIGHT(HK2,LEN(HK2)-7)))

 

[pawest]

You're good, PGC... You're good.

Thanks a lot! It seems to work perfectly.

For a learning exercise, can you explain a little bit more about this part of the function: LOOKUP(2,1/SEARCH({"FEE","REBATE"},HK2),{"FEE","REBATE"})

Thanks... I think I understand exactly what it does but I'd like to hear your take on it.