Unable to extract number from text (in 2007 excel)

[manish.agrawal446@gmail.com]

Hi,

I was trying to find the position of starting number in a text. i Was getting wrong value


Text Num Start Length Number Digits

11000 3 0

1

2

3

4

5

6

7

8

9


Formula to get the starting position for numbers =MIN(IFERROR(FIND(lstNumbers,A2),""))

where lstNumbers is digit range from 0 to 9

 

[NARAYANK991]

Hi Manish ,


Your formula is correct. Please check whether you have done the following :


1. Entered the formula as an array formula , using CTRL SHIFT ENTER.


2. Defined a 10 cell range as lstNumbers , and filled the cells with the digits 0 through 9.


Suppose A2 has the text manish.agrawal446 , then the formula will return 15 , since the digit 4 is the 15th character.


Narayan

 

[manish.agrawal446@gmail.com]

Thanks .. Got it

Can u please let me know the significance of CTRL SHIFT ENTER.

 

[oldchippy]

Hi,


This seems to work just fine as a straight forward formula


=IFERROR(FIND(lstNumbers,$A$2),"")

 

[NARAYANK991]

Hi Manish ,


One good way to understand the significance of the CTRL SHIFT ENTER , is to use the Evaluate Formula facility available in Excel.


If you take the example given earlier i.e. suppose the cell A2 contains the text manish.agrawal446 ; suppose your formula :


=MIN(IFERROR(FIND(lstNumbers,A2),""))


entered as a normal formula ( not using CTRL SHIFT ENTER ) is in cell B1.


Place the cursor in B1 and click on the Evaluate Formula button and step through the complete formula.


At one stage , you will see the result as :


MIN(IFERROR(FIND(6,"manish.agrawal446"),"")


This 6 is actually some random number ; if you experiment and see , you will find that changing the named range lstNumbers , from one set of addresses to another , will change this from 6 to 9 , which will result in a #VALUE! output !


Now , enter the above formula , this time using CTRL SHIFT ENTER , in cell B2. Place the cursor in B2 and click on the Evaluate Formula button and step through the complete formula.


At the same stage , you will now see :


MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9},"manish.agrawal446"),"")


The array formula is evaluated using all the digits specified in the range lstNumbers ; the non-array formula is evaluated using only one digit. The results of these two methods of evaluation can be vastly different.


Narayan