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

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

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"MJW" wrote:

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

Hi Pranav,

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

Thanks,

Jamie

"Pranav Vaidya" wrote:

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

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"MJW" wrote:

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

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


"MJW" wrote:

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

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


"Elkar" wrote:

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


"MJW" wrote:

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

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


"MJW" wrote:

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


"Elkar" wrote:

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


"MJW" wrote:

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

This will return 3 opr blank space

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

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"MJW" wrote:

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

Hi,

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

Thanks,
--
Farhad Hodjat


"MJW" wrote:

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

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

"Farhad" wrote:

Hi,

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

Thanks,
--
Farhad Hodjat


"MJW" wrote:

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