Solved

Extracting numbers from text

Posted on 2014-03-11
9
216 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

20 Experts available now in Live!

Get 1:1 Help Now