separate numbers from text

Victtor

Victtor

Board Regular
Joined
Jan 4, 2007
Messages
170
Office Version
  1. 365
Platform
  1. Windows
Most of my data comes like this in 1 cell (Test to columns does not work in this case):

2037020 Pint Milk 32 4

I need to extract that number 32

the Pint Milk could be any of my inventory items

How can I accomplish this. I do not need this hard coded for just Pint Milk, but for any of my items:

2037020 Pint Milk 32 4
2037021 Yup 5 0
2037022 Simply Juice 47 6
2037024 20oz Reg
2037025 Gold Tea 35 4
2037030 Minute Maid 66 8
2037031 HH Coke 12 1


I need the first number(s) after the inventory item. In the case of the Pint Milk it would be 32. Not the 4.

Please assume the data starts in C2 and I need the result in C3

Please help.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this formula:
Code: 
=TRIM(LEFT(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),110),100))
 
Upvote 0
Joe4 said:
Try this formula:
Code: 
=TRIM(LEFT(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),110),100))
Click to expand...

Works like a charm. Since you answered that so well, how can I extract the item name from this as well? If the data is in C2, can I get the product name in D2 and the quantity in E2?
 
Upvote 0
Code: 
[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64, align: left"] =MID(A1,SEARCH("Pint Milk  ",A1,1)+LEN("Pint Milk "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A2,SEARCH("Yup  ",A2,1)+LEN("Yup "),1)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A3,SEARCH("Simply Juice  ",A3,1)+LEN("Simply Juice "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A4,SEARCH("2037024  ",A4,1)+LEN("2037024 "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A5,SEARCH("Gold Tea  ",A5,1)+LEN("Gold Tea "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A6,SEARCH("Minute Maid  ",A6,1)+LEN("Minute Maid "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A7,SEARCH(" HH Coke  ",A7,1)+LEN(" HH Coke "),2)[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Dossfm0q said:
Code: 
[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64, align: left"] =MID(A1,SEARCH("Pint Milk  ",A1,1)+LEN("Pint Milk "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A2,SEARCH("Yup  ",A2,1)+LEN("Yup "),1)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A3,SEARCH("Simply Juice  ",A3,1)+LEN("Simply Juice "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A4,SEARCH("2037024  ",A4,1)+LEN("2037024 "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A5,SEARCH("Gold Tea  ",A5,1)+LEN("Gold Tea "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A6,SEARCH("Minute Maid  ",A6,1)+LEN("Minute Maid "),2)[/TD]
 [/TR]
 [TR]
  [TD="align: left"] =MID(A7,SEARCH(" HH Coke  ",A7,1)+LEN(" HH Coke "),2)[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

The only problem with this is that it is hard coded. I have hundreds of inventory items
 
Upvote 0
Maybe something like this:
Code: 
=TRIM(LEFT(SUBSTITUTE(MID(C2,9,LEN(C2))," ",REPT(" ",100)),LEN(SUBSTITUTE(MID(C2,9,LEN(C2))," ",REPT(" ",100)))-200))
 
Upvote 0
Victtor said:
Works like a charm. Since you answered that so well, how can I extract the item name from this as well? If the data is in C2, can I get the product name in D2 and the quantity in E2?
Click to expand...
Did Joe4's formula give you the answer you wanted for this item...

2037024 20oz Reg

If not, what should the answer have been? Also, for your additional question, what is the item name for the above item?
 
Upvote 0
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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p> </o:p></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:p> </o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p> </o:p></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:p> </o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p></o:p></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:p> </o:p></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:p> </o:p></span></p></td></tr></table><p class=MsoNormal style='text-align:left;direction:ltr;unicode-bidi:embed'><o:p> </o:p></p></div></body></htm

<o:p> </o:p>​
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA to compare values in two columns i.e (Id Number and Dates) on separate sheets for unique values then extract row content to third sheet
Replies
3
Views
510
Legacy 143009
L
F
  • Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
581
ExceLoki
ExceLoki
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
501
fjns
F
V
  • Solved
How to keep data cell count info after clearing cells
Replies
1
Views
256
Herakles
H
K
  • Solved
Extract number only from “mix-up text & numbers data row”
Replies
7
Views
457
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,700
Messages
6,126,302
Members
449,308
Latest member
VerifiedBleachersAttendee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top