Is it possible to use the Search (or Find) function to find a number in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.

SEARCH will return the starting position number of the search criteria. Is
that what you want?

How about posting several representative samples of your data so we can what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a number in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.

OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from each
cell.
The numerical part is always 7 characters long, but I don't know where it
begins in the string, or how many non-numerical characters there may be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then use
that as the starting point for the MID() function, which can extract the 7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search criteria. Is
that what you want?

How about posting several representative samples of your data so we can what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a number in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.

The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number string.
Excel doesn't recognize leading 0s as part of a *numeric number*. If there
might be leading 0s you can either use a different formula to extract the
string as a TEXT value and retain any leading 0s or you can use the above
formula which extracts the string as a NUMERIC NUMBER and then apply a
custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from each
cell.
The numerical part is always 7 characters long, but I don't know where it
begins in the string, or how many non-numerical characters there may be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then use
that as the starting point for the MID() function, which can extract the 7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search criteria.
Is
that what you want?

How about posting several representative samples of your data so we can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a number in
a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.

Dave

'to return the first position of a numeric within the text
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

'So for your data
=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),7)

--
Jacob


"Dave" wrote:

Is it possible to use the Search (or Find) function to find a number in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.

Improvement...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

In this case, since the number is a specific length the LOOKUP function is
redundant.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number
string. Excel doesn't recognize leading 0s as part of a *numeric number*.
If there might be leading 0s you can either use a different formula to
extract the string as a TEXT value and retain any leading 0s or you can
use the above formula which extracts the string as a NUMERIC NUMBER and
then apply a custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from
each
cell.
The numerical part is always 7 characters long, but I don't know where it
begins in the string, or how many non-numerical characters there may be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then use
that as the starting point for the MID() function, which can extract the
7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search criteria.
Is
that what you want?

How about posting several representative samples of your data so we can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a number
in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.

Thanks Jacob. Perfect.
Regards - Dave.

"Jacob Skaria" wrote:

Dave

'to return the first position of a numeric within the text
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

'So for your data
=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),7)

--
Jacob


"Dave" wrote:

Is it possible to use the Search (or Find) function to find a number in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.

Hi Biff,
Thanks - perfect.
I haven't seen this use of FIND, either the curly bracket part, or the
A1&"0123456789"
Could you explain that please, especially the 2nd part.
Regards - Dave.
PS, I'm not getting notified of replies in my email, even though I tick the
box. Is that happening to everyone, or just me?

"T. Valko" wrote:

Improvement...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

In this case, since the number is a specific length the LOOKUP function is
redundant.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number
string. Excel doesn't recognize leading 0s as part of a *numeric number*.
If there might be leading 0s you can either use a different formula to
extract the string as a TEXT value and retain any leading 0s or you can
use the above formula which extracts the string as a NUMERIC NUMBER and
then apply a custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from
each
cell.
The numerical part is always 7 characters long, but I don't know where it
begins in the string, or how many non-numerical characters there may be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then use
that as the starting point for the MID() function, which can extract the
7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search criteria.
Is
that what you want?

How about posting several representative samples of your data so we can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a number
in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.





.

(2nd attempt to post this reply. I got "burned" by the x_y_z rule!)

Let's take a look at this example.

A1 = x12x

We want to extract the numeric portion of that string. We know that the
number will *always* be 2 consecutive digits but they could be located
anywhere in the string:

12xx
x12x
xx12

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)

Returns 12 (as a numeric number)

Here's how it works...

First thing we need to do is find the location of the first digit within the
string. Since the number in the string can contain any of the digits 0 to 9
we have to look for each of those individual digits.

FIND returns the starting position number of the substring to look for
within the string. For example:

A1 = abc

FIND("a",A1) = 1, the substring "a" is located at position 1 within the
string
abc

FIND("b",A1) = 2, the substring "b" is located at position 2 within the
string
abc

FIND("c",A1) = 3, the substring "c" is located at position 3 within the
string
abc

FIND("z",A1) = #VALUE!, the substring "z" can not be found in the string
abc

Note that FIND is case sensitive.

FIND("A",A1) = #VALUE!, the substring "A" can not be found in the string
abc.

Also, FIND will "find" the first instance of the substring within the
string.

A1 = x12x

FIND("x",A1) = 1, the first instance of x is located at position 1

So, we use FIND to get the starting position numbers of the digits 0 to 9 in
the string x12x. The problem with this is that since the number in the
string is only 2 digits we will get those #VALUE! errors for the digits that
can't be found. For example:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1)

FIND(0,A1) = #VALUE!
FIND(1,A1) = 2
FIND(2,A1) = 3
FIND(3,A1) = #VALUE!
FIND(4,A1) = #VALUE!
FIND(5,A1) = #VALUE!
FIND(6,A1) = #VALUE!
FIND(7,A1) = #VALUE!
FIND(8,A1) = #VALUE!
FIND(9,A1) = #VALUE!

So, if we try that approach the result of the formula will be the error
#VALUE!. That doesn't help us do what we need to do!

Here's how we get around all those #VALUE! errors. We make sure that every
digit can be found in the string. We do that by concatenating all of the
individual digits to the end of the string:

A1&"0123456789"

Now FIND looks for the digits in the string:

x12x0123456789

This ensures that each digit will be found and eliminates the #VALUE! error
problem.

So:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

FIND(0,A1&"0123456789") = 5
FIND(1,A1&"0123456789") = 2
FIND(2,A1&"0123456789") = 3
FIND(3,A1&"0123456789") = 8
FIND(4,A1&"0123456789") = 9
FIND(5,A1&"0123456789") = 10
FIND(6,A1&"0123456789") = 11
FIND(7,A1&"0123456789") = 12
FIND(8,A1&"0123456789") = 13
FIND(9,A1&"0123456789") = 14

These position numbers are then passed to the MIN function

MIN({5,2,3,8,9,10,11,12,13,14})

MIN returns the minimum number from the array {5,2,3,8,9,10,11,12,13,14} =
2. This is the starting position of the first digit within the string x12x.
The MIN number is then passed to the MID function:

MID(A1,2,2)

Starting at position 2 of the string x12x return the next 2 characters =
"12". MID *always* returns a TEXT value even if it looks like a number. To
convert that TEXT "12" to the numeric number 12 we use the double unary
minus --.

--MID(A1,2,2)

So, to extract the numeric portion of the string x12x:

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)


exp101
--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks - perfect.
I haven't seen this use of FIND, either the curly bracket part, or the
A1&"0123456789"
Could you explain that please, especially the 2nd part.
Regards - Dave.
PS, I'm not getting notified of replies in my email, even though I tick
the
box. Is that happening to everyone, or just me?

"T. Valko" wrote:

Improvement...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

In this case, since the number is a specific length the LOOKUP function
is
redundant.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number
string. Excel doesn't recognize leading 0s as part of a *numeric
number*.
If there might be leading 0s you can either use a different formula to
extract the string as a TEXT value and retain any leading 0s or you can
use the above formula which extracts the string as a NUMERIC NUMBER and
then apply a custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from
each
cell.
The numerical part is always 7 characters long, but I don't know where
it
begins in the string, or how many non-numerical characters there may
be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then
use
that as the starting point for the MID() function, which can extract
the
7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search
criteria.
Is
that what you want?

How about posting several representative samples of your data so we
can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a
number
in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm
after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.





.

Hi Bif,
While looking for something else, I came across this post. I don't remember
getting it at the time, and it appears that I haven't responded to it. Sorry.
Thanks for the explanation. Concatonating the numbers to the original cell
value is brilliant. I'd never have thought of that.
Regards - Dave.

"T. Valko" wrote:

(2nd attempt to post this reply. I got "burned" by the x_y_z rule!)

Let's take a look at this example.

A1 = x12x

We want to extract the numeric portion of that string. We know that the
number will *always* be 2 consecutive digits but they could be located
anywhere in the string:

12xx
x12x
xx12

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)

Returns 12 (as a numeric number)

Here's how it works...

First thing we need to do is find the location of the first digit within the
string. Since the number in the string can contain any of the digits 0 to 9
we have to look for each of those individual digits.

FIND returns the starting position number of the substring to look for
within the string. For example:

A1 = abc

FIND("a",A1) = 1, the substring "a" is located at position 1 within the
string
abc

FIND("b",A1) = 2, the substring "b" is located at position 2 within the
string
abc

FIND("c",A1) = 3, the substring "c" is located at position 3 within the
string
abc

FIND("z",A1) = #VALUE!, the substring "z" can not be found in the string
abc

Note that FIND is case sensitive.

FIND("A",A1) = #VALUE!, the substring "A" can not be found in the string
abc.

Also, FIND will "find" the first instance of the substring within the
string.

A1 = x12x

FIND("x",A1) = 1, the first instance of x is located at position 1

So, we use FIND to get the starting position numbers of the digits 0 to 9 in
the string x12x. The problem with this is that since the number in the
string is only 2 digits we will get those #VALUE! errors for the digits that
can't be found. For example:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1)

FIND(0,A1) = #VALUE!
FIND(1,A1) = 2
FIND(2,A1) = 3
FIND(3,A1) = #VALUE!
FIND(4,A1) = #VALUE!
FIND(5,A1) = #VALUE!
FIND(6,A1) = #VALUE!
FIND(7,A1) = #VALUE!
FIND(8,A1) = #VALUE!
FIND(9,A1) = #VALUE!

So, if we try that approach the result of the formula will be the error
#VALUE!. That doesn't help us do what we need to do!

Here's how we get around all those #VALUE! errors. We make sure that every
digit can be found in the string. We do that by concatenating all of the
individual digits to the end of the string:

A1&"0123456789"

Now FIND looks for the digits in the string:

x12x0123456789

This ensures that each digit will be found and eliminates the #VALUE! error
problem.

So:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

FIND(0,A1&"0123456789") = 5
FIND(1,A1&"0123456789") = 2
FIND(2,A1&"0123456789") = 3
FIND(3,A1&"0123456789") = 8
FIND(4,A1&"0123456789") = 9
FIND(5,A1&"0123456789") = 10
FIND(6,A1&"0123456789") = 11
FIND(7,A1&"0123456789") = 12
FIND(8,A1&"0123456789") = 13
FIND(9,A1&"0123456789") = 14

These position numbers are then passed to the MIN function

MIN({5,2,3,8,9,10,11,12,13,14})

MIN returns the minimum number from the array {5,2,3,8,9,10,11,12,13,14} =
2. This is the starting position of the first digit within the string x12x.
The MIN number is then passed to the MID function:

MID(A1,2,2)

Starting at position 2 of the string x12x return the next 2 characters =
"12". MID *always* returns a TEXT value even if it looks like a number. To
convert that TEXT "12" to the numeric number 12 we use the double unary
minus --.

--MID(A1,2,2)

So, to extract the numeric portion of the string x12x:

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)


exp101
--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks - perfect.
I haven't seen this use of FIND, either the curly bracket part, or the
A1&"0123456789"
Could you explain that please, especially the 2nd part.
Regards - Dave.
PS, I'm not getting notified of replies in my email, even though I tick
the
box. Is that happening to everyone, or just me?

"T. Valko" wrote:

Improvement...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

In this case, since the number is a specific length the LOOKUP function
is
redundant.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number
string. Excel doesn't recognize leading 0s as part of a *numeric
number*.
If there might be leading 0s you can either use a different formula to
extract the string as a TEXT value and retain any leading 0s or you can
use the above formula which extracts the string as a NUMERIC NUMBER and
then apply a custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters from
each
cell.
The numerical part is always 7 characters long, but I don't know where
it
begins in the string, or how many non-numerical characters there may
be.
So I want to search for the first number (SEARCH(CODE(48 to 57), then
use
that as the starting point for the MID() function, which can extract
the
7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search
criteria.
Is
that what you want?

How about posting several representative samples of your data so we
can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a
number
in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm
after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.





.



.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Bif,
While looking for something else, I came across this post. I don't
remember
getting it at the time, and it appears that I haven't responded to it.
Sorry.
Thanks for the explanation. Concatonating the numbers to the original cell
value is brilliant. I'd never have thought of that.
Regards - Dave.

"T. Valko" wrote:

(2nd attempt to post this reply. I got "burned" by the x_y_z rule!)

Let's take a look at this example.

A1 = x12x

We want to extract the numeric portion of that string. We know that the
number will *always* be 2 consecutive digits but they could be located
anywhere in the string:

12xx
x12x
xx12

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)

Returns 12 (as a numeric number)

Here's how it works...

First thing we need to do is find the location of the first digit within
the
string. Since the number in the string can contain any of the digits 0 to
9
we have to look for each of those individual digits.

FIND returns the starting position number of the substring to look for
within the string. For example:

A1 = abc

FIND("a",A1) = 1, the substring "a" is located at position 1 within the
string
abc

FIND("b",A1) = 2, the substring "b" is located at position 2 within the
string
abc

FIND("c",A1) = 3, the substring "c" is located at position 3 within the
string
abc

FIND("z",A1) = #VALUE!, the substring "z" can not be found in the string
abc

Note that FIND is case sensitive.

FIND("A",A1) = #VALUE!, the substring "A" can not be found in the string
abc.

Also, FIND will "find" the first instance of the substring within the
string.

A1 = x12x

FIND("x",A1) = 1, the first instance of x is located at position 1

So, we use FIND to get the starting position numbers of the digits 0 to 9
in
the string x12x. The problem with this is that since the number in the
string is only 2 digits we will get those #VALUE! errors for the digits
that
can't be found. For example:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1)

FIND(0,A1) = #VALUE!
FIND(1,A1) = 2
FIND(2,A1) = 3
FIND(3,A1) = #VALUE!
FIND(4,A1) = #VALUE!
FIND(5,A1) = #VALUE!
FIND(6,A1) = #VALUE!
FIND(7,A1) = #VALUE!
FIND(8,A1) = #VALUE!
FIND(9,A1) = #VALUE!

So, if we try that approach the result of the formula will be the error
#VALUE!. That doesn't help us do what we need to do!

Here's how we get around all those #VALUE! errors. We make sure that
every
digit can be found in the string. We do that by concatenating all of the
individual digits to the end of the string:

A1&"0123456789"

Now FIND looks for the digits in the string:

x12x0123456789

This ensures that each digit will be found and eliminates the #VALUE!
error
problem.

So:

A1 = x12x

FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

FIND(0,A1&"0123456789") = 5
FIND(1,A1&"0123456789") = 2
FIND(2,A1&"0123456789") = 3
FIND(3,A1&"0123456789") = 8
FIND(4,A1&"0123456789") = 9
FIND(5,A1&"0123456789") = 10
FIND(6,A1&"0123456789") = 11
FIND(7,A1&"0123456789") = 12
FIND(8,A1&"0123456789") = 13
FIND(9,A1&"0123456789") = 14

These position numbers are then passed to the MIN function

MIN({5,2,3,8,9,10,11,12,13,14})

MIN returns the minimum number from the array {5,2,3,8,9,10,11,12,13,14}
=
2. This is the starting position of the first digit within the string
x12x.
The MIN number is then passed to the MID function:

MID(A1,2,2)

Starting at position 2 of the string x12x return the next 2 characters =
"12". MID *always* returns a TEXT value even if it looks like a number.
To
convert that TEXT "12" to the numeric number 12 we use the double unary
minus --.

--MID(A1,2,2)

So, to extract the numeric portion of the string x12x:

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),2)


exp101
--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks - perfect.
I haven't seen this use of FIND, either the curly bracket part, or the
A1&"0123456789"
Could you explain that please, especially the 2nd part.
Regards - Dave.
PS, I'm not getting notified of replies in my email, even though I tick
the
box. Is that happening to everyone, or just me?

"T. Valko" wrote:

Improvement...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

In this case, since the number is a specific length the LOOKUP
function
is
redundant.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The numerical part is always 7 characters long

Try this...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7))

Note that this formula will strip off any leading 0s from the number
string. Excel doesn't recognize leading 0s as part of a *numeric
number*.
If there might be leading 0s you can either use a different formula
to
extract the string as a TEXT value and retain any leading 0s or you
can
use the above formula which extracts the string as a NUMERIC NUMBER
and
then apply a custom number format to display any leading 0s.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
OK,
Ex.1 qwertyabc1234567
Ex.2 poi4567365fgher
Ex.3 6945908FGTEy

I would like to be able to extract just the numerical characters
from
each
cell.
The numerical part is always 7 characters long, but I don't know
where
it
begins in the string, or how many non-numerical characters there
may
be.
So I want to search for the first number (SEARCH(CODE(48 to 57),
then
use
that as the starting point for the MID() function, which can
extract
the
7
digit number if it knows where to start.
Sorry I was vague before. Hope this is better.
Regards - Dave.

"T. Valko" wrote:

SEARCH will return the starting position number of the search
criteria.
Is
that what you want?

How about posting several representative samples of your data so
we
can
what
you're trying to do.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is it possible to use the Search (or Find) function to find a
number
in a
text string?
eg SEARCH(CODE(<57),A1)
This doesn't work, but hopefully it gives an idea of what I'm
after.
Ideally, I'd like:
SEARCH(CODE(48 to 57),A1)
Possible?
Regards - Dave.


.





.



.