How can you make the Search function find a number (any number) in a text
string?

In this example, I would like Search to locate the first number (not just
the number 1, which is in the formula below) after a defined point in the
text string:

SEARCH(1,B2,LEN(TEXT(C2,"d,m,yyyy"))+15)

--
iperlovsky

Hello,

If I understand what you are trying to do, here is one way to do it.

To find the location of the first numeric character (from the left,
including spaces):

=IF(offset<LEN(A1),IF(MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},RIGHT(A1,LEN(A1)-offset)&"0123456789"))LEN(A1)-offset,"No
Num",MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,LEN (A1)-offset)&"0123456789"))),IF(A1="","","Reduce
the Offset"))

The formula will check for a numeric character within the string and if none
is found it will return "No Num", otherwise it will return the position of
the first numeric character. The formula also allows for an offset from the
left, meaning a certain number of characters at the beginning of the string
can be ignored in the calculations. Create a named range ("offset")
referencing a single cell and insert the desired value in that cell
representing the number of characters to ignore. If the offset value
happens to be greater than or equal to the string length, the formula
returns "Reduce the Offset".

If you wish to know the value of the first numeric character found in the
string, use the following formula:

=IF(offset<LEN(A2),IF(MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},RIGHT(A2,LEN(A2)-offset)&"0123456789"))LEN(A2)-offset,"No
Num",RIGHT(LEFT(RIGHT(A2,LEN(A2)-offset),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A2, LEN(A2)-offset)&"0123456789"))),1)),IF(A2="","","Reduce
the Offset"))

Bob



"IPerlovsky" wrote in message
...
How can you make the Search function find a number (any number) in a text
string?

In this example, I would like Search to locate the first number (not just
the number 1, which is in the formula below) after a defined point in the
text string:

SEARCH(1,B2,LEN(TEXT(C2,"d,m,yyyy"))+15)

--
iperlovsky