抽出某一字前面的数字

江夏雁

2033

收藏 2015-03-19

如果想把数据从左开始向右找，找到“支”这个字，就把“支”字之前的数据抽出单列一列，怎么办？

比如

雪茄烟|烟草制|高希霸牌|1X25支 5盒

想抽出“25”这个数字
多谢！

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

全部回复

pingguagain

2015-3-19 23:38:18

Using the following VBA code will fulfill your requirement.

Sub InsertCol()

Dim iRowCount As Integer
Dim iColCount As Integer
Dim i As Integer
Dim j As Integer
Dim iPosStr As Integer
Dim iPosTimes As Integer
Dim NumStr As String
Dim myRng As Range

iRowCount = ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count
iColCount = ThisWorkbook.Sheets("Sheet1").UsedRange.Columns.Count

For i = 1 To iRowCount
For j = 1 To iColCount
      iPosStr = InStr(ThisWorkbook.Sheets("Sheet1").Cells(i, j), "Ö§")

      If iPosStr > 1 Then
         iPosTimes = InStr(ThisWorkbook.Sheets("Sheet1").Cells(i, j), "X")
         NumStr = Mid(ThisWorkbook.Sheets("Sheet1").Cells(i, j), iPosTimes + 1, iPosStr - iPosTimes - 1)
         'MsgBox NumStr
         Set myRng = ThisWorkbook.Sheets("sheet1").Range(Cells(i, j), Cells(i, iColCount))
         myRng.Insert Shift:=xlToRight
         ThisWorkbook.Sheets("Sheet1").Cells(i, j) = NumStr
      End If
Next j
Next i

End Sub