如何在Excel中实时验证和更正数据? [英] How to validate and correct data in excel in realtime?

问题描述

我知道excel具有数据验证功能，但是有一种方法可以自动为用户应用更改，而不是提示他们有问题吗?

I know excel has the data validation feature but is there a way to automatically apply the changes for the user instead of prompting them that there is an issue?

例如，我们为内部系统提供了一个通用电子表格模板来添加项目.在第一列中，对于项目号，不能有任何非法字符.因此，如果用户在A1中输入AN-XR10LP/1，然后单击下一个单元格，则我希望验证无需用户执行任何操作即可将A1值更正为ANXR10LP1.

For example, we have a general spreadsheet template for our internal system to add items. In the first column, for Item No, there can't be any illegal characters. So if the user enters AN-XR10LP/1 in A1 and then clicks on the next cell, I would want the validation to correct the A1 value to be ANXR10LP1 without the user doing anything.

关于如何开始的任何想法?

Any ideas on how I can start with this?

推荐答案

我认为您只能使用VBA来完成此任务.尝试下面的代码.如果您在单元格上具有数据验证"，则可能需要对其进行调整.

I think you can only accomplish this with VBA. Try the code below. It may need to be tweaked if you have Data Validation on the cells.

将此模块放在要验证数据的工作表对象中.

Place this module in the Worksheet Object where you want your data validated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Rows.Count = 1 Then '-> make sure row count is 1 and target is in column A, can further refine if needed

    Dim strValue As String

    strValue = CleanString(Target.Value)

    Application.EnableEvents = False
    Target = strValue
    Application.EnableEvents = True

End If

End Sub

Function CleanString(str As String) As String

CleanString = Replace(str, "-", "")
CleanString = Replace(CleanString, "/", "")
'...
'keep adding replacements as needed


End Function

这篇关于如何在Excel中实时验证和更正数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！