Macro for extracting multiple decimal values from a single string

[mlewand420]

Hello All,

I have what I though was a simple problem however, its becoming quite a challenge with little or no precedence on the web for reference.

Ideally, I would love to have a macro (or vba) that will simply look at all of column "B" and extract the corresponding decimal value, percent value or numerical values to another same row column.

IS THIS EVEN Possible?

For Example:

[TABLE="class: grid, width: 735, align: left"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]TEXT[/TD]
[TD]Need 1st decimal number or number in string here[/TD]
[TD]Need 2nd decimal number or number in string here[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]Rates from .0008C to .0469E[/TD]
[TD="align: right"]0.0008[/TD]
[TD="align: right"]0.0469[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]PROGRAM From 3267 To 3269[/TD]
[TD="align: right"]3267[/TD]
[TD="align: right"]3269[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Percents from 49% to 87%[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]87[/TD]
[/TR]
</tbody>[/TABLE]












Additional Considerations:

1 - I have been successful in using the following FORMULA, however it work only for the first occurrence of a number. THE REAL TRICK IS GETTING THE SECOND NUMBER from starting from the RIGHT. =LOOKUP(9000000000+307,--LEFT(MID(B3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},$B3&"1023456789")),999),ROW(INDIRECT("1:999"))))*0.0001 --> (Gives me | 0.0008 | above)

2 - The numbers may vary for example .009, 347, 8% etc... withing the text strings.

3 - Follow on calculations would then be installed into COL E to figure a variance.

4 - A MACRO or VBA script would be great but if you can create function to assist, that would be fine too.

5 - THIS WOULD BE HELPING LOTS OF PEOPLE SAVE TIME AND MONEY IF I COULD INTRODUCE THIS TOOL TO OUR PROCESS!

Please let me know if you need anything further from me to help with this puzzle.

 

[Akuini]

Try this:

[FONT=lucida console][color=Royalblue]Sub[/color] a1092781a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092781-macro-extracting-multiple-decimal-values-single-string.html[/color][/i]

[color=Royalblue]Dim[/color] r [color=Royalblue]As[/color] Range
Application.ScreenUpdating = [color=Royalblue]False[/color]
[color=Royalblue]For[/color] [color=Royalblue]Each[/color] r [color=Royalblue]In[/color] Range([color=brown]"A2"[/color], Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp))
    
    ary = Split(r, [color=brown]" "[/color])
    [color=Royalblue]For[/color] i = LBound(ary) [color=Royalblue]To[/color] UBound(ary)
        [color=Royalblue]If[/color] LCase(ary(i)) = [color=brown]"from"[/color] [color=Royalblue]Then[/color] r.Offset(, [color=crimson]1[/color]) = Val(ary(i + [color=crimson]1[/color]))
        [color=Royalblue]If[/color] LCase(ary(i)) = [color=brown]"to"[/color] [color=Royalblue]Then[/color] r.Offset(, [color=crimson]2[/color]) = Val(ary(i + [color=crimson]1[/color]))
    
    [color=Royalblue]Next[/color]

[color=Royalblue]Next[/color]
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color]
[/FONT]

Result:


Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td]TEXT[/td][td]Need 1st decimal number or number in string here[/td][td]Need 2nd decimal number or number in string here[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td]Rates from .0008C to .0469E[/td][td]

0,0008​

[/td][td]

0,0469​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td]PROGRAM From 3267 To 3269[/td][td]

3267​

[/td][td]

3269​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td]Percents from 49% to 87%[/td][td]

49​

[/td][td]

87​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]