Solved

Extracting numbers from text

Posted on 2014-03-11
9
218 Views
Last Modified: 2014-03-11
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
0
Comment
Question by:Frank Freese
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 39922067
in the RED table your formula has in each row reference to A2 that´s why 678 is in the all table (B column)
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
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39922076
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.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39922081
Beat me to it helpfinder.   :)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Frank Freese
ID: 39922140
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
0
 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
ID: 39922178
put this formula in the B2 cilumn (reffering to Book2.xlsx)
=1*MID(A2;MATCH(FALSE;ISERROR(1*MID(A2;ROW($1:$10);1));0);255)
then edit the cell (F2 or double click) and CTRL+SHIFT+ENTER
then drop any copy down the formula to B11

PS: you may need to change semicolons (;) in my formula to commas (,). It depends on your Regional settings.
0
 

Author Comment

by:Frank Freese
ID: 39922204
ok...that worked with ","
thanks
0
 

Author Closing Comment

by:Frank Freese
ID: 39922205
thank you
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39922217
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
0
 

Author Comment

by:Frank Freese
ID: 39922255
thanks for the tip barry
I always appreciate your input
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question