Solved

Extracting numbers from text

Posted on 2014-03-11
9
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

635 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