Solved

Extracting numbers from text

Posted on 2014-03-11
9
215 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
Comment Utility
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
Comment Utility
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
Comment Utility
Beat me to it helpfinder.   :)
0
 

Author Comment

by:Frank Freese
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
Comment Utility
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
Comment Utility
ok...that worked with ","
thanks
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
thank you
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
Comment Utility
thanks for the tip barry
I always appreciate your input
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now