Solved

Error in extracting values from text

Posted on 2013-12-21
11
243 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 81

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 81

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
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 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 81

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

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

Title # Comments Views Activity
macro for each dropdown 15 45
Highlighting cells in Excel 9 17
Excel Hyperlink Question 4 30
Converting An Excel IF statement formula to   VBA 10 23
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

910 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

22 Experts available now in Live!

Get 1:1 Help Now