Error in extracting values from text

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
Frank FreeseAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
byundtConnect With a Mentor Commented:
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
 
Frank FreeseAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
barry houdiniCommented:
What do you want the formula to do, you haven't said?

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

regards, barry
0
 
Frank FreeseAuthor Commented:
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
 
barry houdiniCommented:
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
 
Frank FreeseAuthor Commented:
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
 
Frank FreeseAuthor Commented:
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
 
byundtConnect With a Mentor Commented:
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
 
Frank FreeseAuthor Commented:
Confusing and annoying is an understatement, but thanks for the follow-up. You've been a great help...
Merry Christmas
0
 
Frank FreeseAuthor Commented:
Thank you again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.