Autocorrect user input full stop(period or dot) automatically in excel-CodePudding

I'm working on the user enters the value like R11200 or R11.200 in the cells. So if enters the value with dot(full stop or period) like r11.200, the function or formula should remove the dot automatically to R11200 . I'm tried with autocorrect and it doesn't work.

Does anybody give me a solution? Thanks

CodePudding user response：

If you only need to get rid of dot ( . ), you can use SUBSTITUTE formula =SUBSTITUTE(A1,".","") Output:

Also you can use Data Validation with custom formula to prevent user from entering various symbols:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1:A2,ROW(INDIRECT("1:"&LEN(A1:A2))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

In this case only uppercase letters, lowercase letters and numbers are allowed.

CodePudding user response：

To automatically remove the dot in the same cell you need VBA programming (Worksheet change event). But with data validation, you could force the user to put in the data without a dot.

Formula:

=NOT(ISNUMBER(FIND(".";J2)))

Replace semicolon with comma, if your Excel version needs it.

You can create a custom Error message