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
Solved

Error in extracting values from text

Posted on 2013-12-21
11
245 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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