It is work for me
Code:
Public Sub ExtractNum() Dim LRw As Long, Rng As Range
LRw = Cells(Rows.Count, "A").End(xlUp).Row
Dim T As Long, C As String, N As Long
For Each Rng In Range("A1:A" & LRw)
TXT = Rng.Text
N = 0
For T = 1 To Len(TXT)
C = Mid(TXT, T, 1)
If IsNumeric(C) = True And T > 1 Then
If Mid(TXT, T - 1, 1) = " " And N = 0 Or (IsNumeric(Mid(TXT, T - 1, 1)) = True And N <> 0) Then
N = N & C
End If
End If
Next
Rng.Offset(0, 2) = N
Next
End Sub
Or Function
=ExtrNum(A1)
Code:
Public Function ExtrNum(Rng As Range) Dim TXT As String, T As Long, C As String, N As Long
TXT = Rng.Text
N = 0
For T = 1 To Len(TXT)
C = Mid(TXT, T, 1)
If IsNumeric(C) = True And T > 1 Then
If Mid(TXT, T - 1, 1) = " " And N = 0 Or (IsNumeric(Mid(TXT, T - 1, 1)) = True And N <> 0) Then
N = N & C
End If
End If
Next
ExtrNum = N
End Function
</xml><![endif]--></head>****** lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1> <table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 width=532 style='width:399.0pt;margin-left:-.15pt;border-collapse:collapse'><tr style='height:15.0pt'><td width=24 nowrap style='width:.25in;border:solid windowtext 1.0pt;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'> <o
></o
></span></p></td><td width=228 nowrap style='width:170.75pt;border:solid windowtext 1.0pt;border-left:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>A<o
></o
></span></p></td><td width=55 nowrap style='width:41.25pt;border:solid windowtext 1.0pt;border-left:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>B<o
></o
></span></p></td><td width=97 nowrap style='width:73.0pt;border:solid windowtext 1.0pt;border-left:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>C<o
></o
></span></p></td><td width=64 nowrap style='width:48.0pt;border:solid windowtext 1.0pt;border-left:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>D<o
></o
></span></p></td><td width=64 nowrap style='width:48.0pt;border:solid windowtext 1.0pt;border-left:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>E<o
></o
></span></p></td></tr><tr style='height:3.5pt'><td width=24 nowrap style='width:.25in;border:solid windowtext 1.0pt;border-top:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>1<o
></o
></span></p></td><td width=228 nowrap valign=bottom style='width:170.75pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:10.0pt;font-family:"Verdana",sans-serif;color:#333333'>2037020 Pint Milk 32 4<o
></o
></span></p></td><td width=55 nowrap valign=bottom style='width:41.25pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal align=right style='text-align:right;direction:ltr;unicode-bidi:embed'><span style='color:black'>32<o
></o
></span></p></td><td width=97 nowrap valign=bottom style='width:73.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'>=ExtrNum(A1)<o
></o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:3.5pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td></tr><tr style='height:15.0pt'><td width=24 nowrap style='width:.25in;border:solid windowtext 1.0pt;border-top:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>2<o
></o
></span></p></td><td width=228 nowrap valign=bottom style='width:170.75pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:10.0pt;font-family:"Verdana",sans-serif;color:#333333'>2037021 Yup 5 0<o
></o
></span></p></td><td width=55 nowrap valign=bottom style='width:41.25pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=right style='text-align:right;direction:ltr;unicode-bidi:embed'><span style='color:black'>5<o
></o
></span></p></td><td width=97 nowrap valign=bottom style='width:73.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'>=ExtrNum(A2)<o
></o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td></tr><tr style='height:15.0pt'><td width=24 nowrap style='width:.25in;border:solid windowtext 1.0pt;border-top:none;background:#BDD7EE;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=center style='text-align:center;direction:ltr;unicode-bidi:embed'><span style='color:black'>3<o
></o
></span></p></td><td width=228 nowrap valign=bottom style='width:170.75pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='font-size:10.0pt;font-family:"Verdana",sans-serif;color:#333333'>2037022 Simply Juice 47 6<o
></o
></span></p></td><td width=55 nowrap valign=bottom style='width:41.25pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal align=right style='text-align:right;direction:ltr;unicode-bidi:embed'><span style='color:black'>47<o
></o
></span></p></td><td width=97 nowrap valign=bottom style='width:73.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'>=ExtrNum(A3)<o
></o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td><td width=64 nowrap valign=bottom style='width:48.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><span style='color:black'><o
> </o
></span></p></td></tr></table><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o
> </o
></p></div></body></htm
<o
> </o
>