Excel VBA常见问题:如何只保留括号里的数字,*号是汉字?

2015-04-15 15:17 阅读 196 次 评论关闭

Excel VBA常见问题有很多条这样的记录:******(212),****(315),*********(658)。如何只保留括号里的数字,*号是汉字

解答:

设数据在A30单元格 =MID(A30,FIND("(",A30)+1,LEN(A30)-FIND("(",A30)-1)

IF 你的数据都是要求记录中最后面的三码数字
可以试着用简单的方式解决
=RIGHT(A1,3)

又问:我是要合并,你却要拆分!你能告诉我怎样将两列:即“数字列”和“文字列”合并成一列?

解答:试试这个:
Sub Join() '将选择的行几个单元格数值合并到一列的一个单元格
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  On Error Resume Next
  Dim iRows As Long, mRow As Long, ir As Long, ic As Long
  iRows = Selection.Rows.Count
  Set lastcell = Cells.SpecialCells(xlLastCell)
  mRow = lastcell.Row
  If mRow < iRows Then iRows = mRow 'not best but better than nothing
  iCols = Selection.Columns.Count
  For ir = 1 To iRows
     newcell = Trim(Selection.Item(ir, 1).value)
     For ic = 2 To iCols
       trimmed = Trim(Selection.Item(ir, ic).value)
       If Len(trimmed) <> 0 Then newcell = newcell & " " & trimmed
       Selection.Item(ir, ic) = ""
     Next ic
     Selection.Item(ir, 1).value = newcell
  Next ir
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel VBA常见问题:如何只保留括号里的数字,*号是汉字? | 猎微网

评论已关闭!