Delete words in a single column

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
I have column F10 where i have the "Quantity", in each cell, I have a number + cases or each or lbs, or Cases. I want to remove the words, just keep the number. Any help, scripts are welcome. Thank you

Quantity
1 Cases
1 cases
11 lbs
3 each

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,198
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming number always comes first followed by a space.
Excel Workbook
FG
2QuantityNum
31 Cases1
41 cases1
511 lbs11
63 each3
Sheet1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,444
Office Version
  1. 365
Platform
  1. Windows
Is it always a number followed by a space, and then the words?
If so, then you can use "Text to Columns" on that column, choosing a delimiter of a single space (and choosing not to import the second field).
That will just leave you with the numbers.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,420
Office Version
  1. 2013
Platform
  1. Windows
If you want to use VBA
Code:
Sub tr()
Dim c As Range, spl As Variant
With ActiveSheet
    For Each c In .Range("F2", .Cells(Rows.Count, "F").End(xlUp))
        spl = Split(c.Value, " ")
            c = spl(LBound(spl))
    Next
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top