Solved

Error in extracting values from text

Posted on 2013-12-21
11
242 Views
Last Modified: 2013-12-22
Folks,
The array formula below returns #VALUE error. I've attached the worksheet with the data and error. I don't see the problem.
=1*MID(D3,MATCH(FALSE,ISERROR(1*MID(D3,ROW($3:$12),1)),0),255)

Open in new window

Extract.xlsm
0
Comment
Question by:Frank Freese
  • 6
  • 3
  • 2
11 Comments
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 39734087
Because you are testing characters 3 through 12 for a number, you need to add 2 to the result from MATCH. The formula still needs to be array-entered.
=1*MID(D3,2+MATCH(FALSE,ISERROR(1*MID(D3,ROW($3:$12),1)),0),255)

As shown above, the formula returns a #VALUE! error if a non-numeric character follows the first digit.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39734102
If you want to extract the first number that appears in the text, then consider a user-defined function with a worksheet formula like:
=GetNumber(D3)

Use the function wizard the first time you use the so it capitalizes the function name correctly. If you want to specify the first and last characters to check, use the optional parameters iStart and iLast. If you don't use the optional parameters, their values will be assumed as 3 and 12 (respectively)
'Install code in a regular module sheet
Function GetNumber(sText As String, Optional iStart As Integer = 3, Optional iLast As Integer = 12)
Dim i As Integer
GetNumber = ""
For i = iStart To iLast
    If IsNumeric(Mid(sText, i, 1)) Then
        GetNumber = Val(Mid(sText, i))
        Exit For
    End If
Next
End Function

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 39734105
OK, I see the problem with test characters 3 through 12. Made the changes by changing from 3:12 to 1:10 and left out the 2+ before the Match.
=1*MID(D3,MATCH(FALSE,ISERROR(1*MID(D3,ROW($1:$10),1)),0),255)

Open in new window

The only returned value is 123 for all cells. Does that mean I'll have to repeat the array formula for E3:E12?

I also entered in your formula with the same results.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39734111
What do you want the formula to do, you haven't said?

What results do you want for E6.....and E12?

regards, barry
0
 

Author Comment

by:Frank Freese
ID: 39734115
Barry,
What I'm trying to learn to do is extract numeric values embedded in text and the results in E6:E12 as numbers.
Haven't tried the user-defined function yet.
Frank
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Expert Comment

by:barry houdini
ID: 39734125
OK but one of those entries is this

TQ7F548

so you have some letters followed by a number followed by a letter and then another number. Do you want to extract just the first number, 7, or the last number 548, or all of the numbers 7548 (or something else)?

regards, barry
0
 

Author Comment

by:Frank Freese
ID: 39734138
OK...(very bad weather here in Memphis right now...dog going nuts!)
If the text string has numeric values embedded that is not contiguous then I would expect the see a #VALUE error. If the numeric values are at the front or back of the string and are contiguous then I would report only the numeric values. The objective is all the numbers.
0
 

Author Comment

by:Frank Freese
ID: 39734168
here's what I did now
In cell E3 I entered:
=1*MID(D3,MATCH(FALSE,ISERROR(1*MID(D3,ROW($1:$10),1)),0),255)
and created an array formula. Then I copied down E3:E12.
I got #VALUE error for any cell where numeric values were embedded or at the beginning of the text string.
It's progress...but for unknown reasons if I select cells E3:E12 first, entered in the formula and created an array formula only the first value in D3 was duplicated in E3:E12, "123"
It would be great to be able to extract numeric values embedded in a text string regardless of where it was, but that may not be possible,
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 39734291
If you select E3:E12 first, you create the same array formula in all 10 cells. Since that particular array formula returns only one value, you get the same result ("123") in all the cells.

Some array formulas can be written so they return a different result in each cell, but I did not succeed when I tried doing so just now. That said, I generally prefer to avoid creating a single array formula across a range of cells because you can't easily extend the range or change the formula in just one of the cells in the range. This is because you must remove the array formula first (such as by Paste Special...Values). Most users find this process both confusing and annoying.
0
 

Author Comment

by:Frank Freese
ID: 39734796
Confusing and annoying is an understatement, but thanks for the follow-up. You've been a great help...
Merry Christmas
0
 

Author Closing Comment

by:Frank Freese
ID: 39734801
Thank you again
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…

707 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

17 Experts available now in Live!

Get 1:1 Help Now