Use of Find with Left, Mid, Right functions in nested IF(and('s

[Guest]

I have a rather large (11k+ rows) sheet with, well, too many columns, to
which I've added an auxiliary column. One column describes the type of
service (support) the row is related to, and is unfortunately text and fairly
inconsistent in certain areas. While I have some experience using the left,
mid and right functions, as well as find, I'm having some difficulty coming
across the syntax required to locate the first " ", and gather the
alphanumeric character that proceeds it. For instance, if a field reads
"Premium 3yr" or "Premium 3 yr", what would be the most efficient formula to
extract only the "3"? Please keep in mind that I need to be able to use the
formula in an existing one with many existing nested IF(and( statements.
Appreciate any help as always.

MJW

 

[Guest]

do you want to search for number 3 always or will ther be different numbers
to search?

 

[Guest]

This simplest way would be:

=MID(A1,FIND(" ",A1)+1,1)

As long as your data remains consistent with the examples you provided, this
should be all you need. However, if you have data such as "Super Premium
3yr" or "Premium 10yr" then you'd need something a bit more complex.

HTH,
Elkar

 

[Guest]

This will return 3 opr blank space

=IF(NOT(ISERROR(FIND("3",A1))),MID(A1,FIND("3",A1),1),"")

 

[Guest]

Hi Pranav,

There will be different values there, depending on the length of service.
Appreciate the effort though.

Thanks,

Jamie

 

[Guest]

Hi,

Are you data elements started with "Premium" and ended with "yr"?

Thanks,

 

[Guest]

Hi Elkar,

Thanks! This worked great... until I discovered yet another inconsistency.
Occasionally, it's reported as "Premium 1 yr", or "Premium1 yr", or "Premium
1yr"... so it seems I'll have to use the whole string in the data validation
and use nested or's within the and's in order to really determine a service
code.

Thanks

Jamie

 

[Guest]

Hi Farhad,

The "yr" is the ending element, but the starting element could be several
different elements, and might include the year value as well due to the
validated column's source being a free-form text field entered manually. I
did create a solution using many nested if's and or's to account for the
correct format and two most-common incorrect formats to a value (IE:
"Premium 3 yr", "Premium3 yr", or "Premium 3yr"), but as noted in a question
I just posted (Subject: "Sometimes you get too close to the tree to see the
forest"), it isn't working... probably due to something incredibly obvious
which my tired eyes aren't seeing.

Thanks,

Jamie

 

[Guest]

Ok, let's see if this works any better.

=IF(RIGHT(A1,2)="yr",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")

HTH,
Elkar