使用VBA删除数字周围的文本 [英] Removing text around a number using VBA

问题描述

我需要从一个单元格中的数字中删除所有文本，然后将两个数字分成两个单元格，并格式化为数字，而不是文本.该单元格包含以下格式的文本/数字:

I need to remove all text from numbers within a cell, then split the two numbers across two cells and be formatted as numbers, not text. The cell contains text/numbers in the following formats:

between 150,000 and 159,999 per annum
between 60 and 65 per hour
between 70.00 and 74.00 per hour

屏幕截图1(之前):

这些中可能还有其他几千行，它们总是从H2开始.两侧都有被占领的牢房.

There may be 1000s of other lines of these and they will always start in H2. There are occupied cells either side.

如果可能的话，代码需要构成一个更大的宏的一部分，该宏在操作前后都有动作，因此能够将其复制并粘贴到中间非常好.

If possible the code needs to form part of a bigger macro that has actions before and after so it would be great to be able to copy and paste it into the middle.

所需结果(之后):

这里是指向示例文档的链接，因为我不确定如何在此处上传- http://www.filedropper.com/sample_13

Here's a link to a sample doc as I'm not sure how to upload on here - http://www.filedropper.com/sample_13

推荐答案

请运行以下步骤:

1. 在H&之间手动添加列我一开始应该是空的.

2. 将此功能添加到您的VBA项目中:

1. add manually column between H & I which should be empty at the beginning.

2. add this function to your VBA project:

Public Function GetNthNumberAlternative(sMark As String, iOrder As Integer) As String

'regexp declaration
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")

With objRegExp
    .Global = True
    .Pattern = "\d+[.,]\d+|\d+"
        GetNthNumberAlternative = .Execute(sMark)(iOrder - 1).Value

End With

End Function

将此子例程添加到您的VBA项目:

add this subroutine to your VBA project:

Sub Run_Function()

Dim Cell As Range, tmpText As String
For Each Cell In Selection.Cells
    tmpText = Cell.Value
    Cell = GetNthNumberAlternative(tmpText, 1)
    Cell.Offset(0, 1) = GetNthNumberAlternative(tmpText, 2)
Next Cell
End Sub

在H列中选择要处理的单元格范围(提示:请在开头选择2-3个单元格以了解其概念)

select range of cells in column H which you want to process (tip: select 2-3 at the beginning to understand the idea)

运行Run_Function()子例程...

已尝试&已针对您提供的示例数据进行了测试！

这篇关于使用VBA删除数字周围的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！