单元格中输入字符串时，如何自动将字符串中的数字设置数值格式？

★子逸☆

各位前辈高人，假如我在单元格range("a1")中输入以下一段文字：
1月份实现销售收入2230521.36元，2月份实现销售收入3215612.55元，增加985091.19元。
如何在Private Sub Worksheet_Change(ByVal Target As Range)事件中，将字符串中的数字设置为"#,##0.00"的数值格式，即上述文字自动变为：
1月份实现销售收入2,230,521.36元，2月份实现销售收入3,215,612.55元，增加985,091.19元。

taller

使用正则匹配，替换就可以了

1. Sub Macro()
   
2.     Dim s1 As String
   
3.     s1 = "1月份实现销售收入2230521.36元，2月份实现销售收入3215612.55元，增加985091.19元。"
   
4.     With CreateObject("VBSCRIPT.REGEXP")
   
5.         .Global = True
   
6.         .Pattern = "\d+.[\d]{2}"
   
7.         Set mh = .Execute(s1)
   
8.         For j = 0 To mh.Count - 1
   
9.             s1 = VBA.Replace(s1, mh(j), VBA.Format(mh(j), "#,##0.00"))
   
10.             'Debug.Print mh(j), VBA.Format(mh(j), "#,##0.00")
    
11.         Next
    
12.         Debug.Print s1
    
13.     End With
    
14. End Sub

复制代码

★子逸☆

taller 发表于 2019-2-17 00:18
使用正则匹配，替换就可以了

感谢您的回复，但达不到我想要的效果。我是想要在单元格输入的字符串中（每次输入都不固定）即包括汉字，也包括数字，然后通过工作表的单元格事件（Target）将字符串中的数字格式自动设置成“#,##0.00"样式。

taller

★子逸☆ 发表于 2019-2-17 12:15
感谢您的回复，但达不到我想要的效果。我是想要在单元格输入的字符串中（每次输入都不固定）即包括汉字， ...

既然你知道使用工作表的单元格事件，那么把代码放在事件中就可以了， s1=Target.Value

★子逸☆

taller 发表于 2019-2-17 14:14
既然你知道使用工作表的单元格事件，那么把代码放在事件中就可以了， s1=Target.Value

我按照您的代码，在工作表 range("a1")中可以达到效果：
Sub Macro()
    Dim s1 As String
    s1 = "1月份实现销售收入2230521.36元，2月份实现销售收入3215612.55元，增加985091.19元。"
    With CreateObject("VBSCRIPT.REGEXP")
        .Global = True
        .Pattern = "\d+.[\d]{2}"
        Set mh = .Execute(s1)
        For j = 0 To mh.Count - 1
             s1 = VBA.Replace(s1, mh(j), VBA.Format(mh(j), "#,##0.00"))
            'Debug.Print mh(j), VBA.Format(mh(j), "#,##0.00")
        Next
        Debug.Print s1
        
        Range("a1") = s1
    End With
End Sub

但在工作表单元格事件中，按下面的代码，在任意单元格中输入“1月份实现销售收入2230521.36元，2月份实现销售收入3215612.55元，增加985091.19元”，变成了“1月份实现销售收入223.0052.001.36元，2月份实现销售收入321.00561.002.55元，增加985.000.009.001.19元”。仍然达不到我想要的效果。
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s1 As String
    s1 = Target.Value
    With CreateObject("VBSCRIPT.REGEXP")
        .Global = True
        .Pattern = "\d+.[\d]{2}"
        Set mh = .Execute(s1)
        For j = 0 To mh.Count - 1
             s1 = VBA.Replace(s1, mh(j), VBA.Format(mh(j), "#,##0.00"))
            'Debug.Print mh(j), VBA.Format(mh(j), "#,##0.00")
        Next
        Debug.Print s1
        
        Target.Value = s1
    End With
End Sub

taller

代码主要功能是对的，如果没有效果就是放置的模块不对。另外需要加两行避免死循环

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim s1 As String
   
3.     s1 = Target.Value
   
4.     With CreateObject("VBSCRIPT.REGEXP")
   
5.         .Global = True
   
6.         .Pattern = "\d+.[\d]{2}"
   
7.         Set mh = .Execute(s1)
   
8.         For j = 0 To mh.Count - 1
   
9.             s1 = VBA.Replace(s1, mh(j), VBA.Format(mh(j), "#,##0.00"))
   
10.             'Debug.Print mh(j), VBA.Format(mh(j), "#,##0.00")
    
11.         Next
    
12.         'Debug.Print s1
    
13.         Application.EnableEvents = False
    
14.         Target.Value = s1
    
15.         Application.EnableEvents = True
    
16.     End With
    
17. End Sub
    

复制代码

taller

demo (2).zip (11.61 KB, 下载次数: 4)

2019-2-18 00:19 上传

点击文件名下载附件

示例文件

一把小刀闯天下

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If CDec(Target.Rows.Count) * Target.Columns.Count > 1 Then Exit Sub
  Dim s As String, mark, i, j, t
  s = Target.Value
  If Len(s) = 0 Then Exit Sub
  Application.EnableEvents = False
  On Error GoTo errmsg
  mark = "0.123456789,"
  For i = Len(s) To 1 Step -1
    If Mid(s, i, 1) = "Ԫ" Then
      For j = i - 1 To 1 Step -1
        If InStr(mark, Mid(s, j, 1)) = 0 Then
          t = Mid(s, j + 1, i - j - 1)
          s = Replace(s, t, Format(Replace(t, ",", vbNullString), "#,##0.00"))
          i = j: Exit For
        End If
      Next
    End If
  Next
  Target.Value = s
  Application.EnableEvents = True
  Exit Sub
errmsg:
  Debug.Print "error"
  Application.EnableEvents = True
End Sub

★子逸☆

taller 发表于 2019-2-18 00:20
示例文件

非常感谢您，但输入的数字只有一位小数时，会出错，如输入“2月份实现销售收入3215612.5元”（小数点后只有一位小数时），得到的结果是“2月份实现销售收入3,215,612.00.5元”，另外，假如“输入1998年”，得到的结果是“1,998.00年”，有什么办法解决输入年份数字时避免出现"#,##0.00"格式。再次感谢您！

★子逸☆

一把小刀闯天下 发表于 2019-2-18 16:37
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

感谢您的回复，但试了下您的代码，好像没反应，达不到预期效果。能否再修正下？