Frank Freese
asked on
Extracting numbers from text
Folks,
I am trying to extract numbers from text. The attached file shows to examples using the same formula with different results and I do not see the differences why. This is an array formula.
Thank
Book1.xlsx
I am trying to extract numbers from text. The attached file shows to examples using the same formula with different results and I do not see the differences why. This is an array formula.
Thank
Book1.xlsx
The formula in E2 was not entered as an array formula (type in the formula and press Ctrl+Shift+Enter)
The rest of the column was entered as an array formula which is why they respond correctly.
The formula in B3-B11 all reference A2 instead of incrementing as you move down the column.
The rest of the column was entered as an array formula which is why they respond correctly.
The formula in B3-B11 all reference A2 instead of incrementing as you move down the column.
Beat me to it helpfinder. :)
ASKER
This is interesting. When I select from the blue table E2:E11, enter in my formula and the press Ctrl+Shift+Enter I get the same results as in the red table? I looked at the red table and it appears that it is an array. I understand that my references are not changing (?) in my array, but why?
I attached a new table not getting the results I am seeking.
Book2.xlsx
I attached a new table not getting the results I am seeking.
Book2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok...that worked with ","
thanks
thanks
ASKER
thank you
There are two ways to enter an "array formula" - either in a single cell or a range of cells - the latter normally only makes sense when the array formula itself returns a range - here your formula returns a single value, so you need to enter the formula in a single cell only, use CTRL+SHIFT+ENTER....and only then copy the formula down.
In the red version you have selected the range and used CTRL+SHIFT+ENTER, hence the same value in every cell.
Note, if you always have numbers at the end, as per your examples, this non-array formula will suffice for up to 9 digits
=LOOKUP(10^9,RIGHT(A2,{1,2 ,3,4,5,6,7 ,8,9})+0)
regards, barry
In the red version you have selected the range and used CTRL+SHIFT+ENTER, hence the same value in every cell.
Note, if you always have numbers at the end, as per your examples, this non-array formula will suffice for up to 9 digits
=LOOKUP(10^9,RIGHT(A2,{1,2
regards, barry
ASKER
thanks for the tip barry
I always appreciate your input
I always appreciate your input
in blue table it if working correctly, but in E2 there you have N/A because you are using array formula so you should enter that E2 cell (double click or F2 key) and not to use ENTER bude CTRL+SHIFT+ENTER