0

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

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

Anand Babu
  • 1
  • 1
  • 3
    You can use [Worksheet_Change](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) event for this. Give it a try and if you are stuck then post the code that you tried and we will take it from there.. – Siddharth Rout Dec 14 '22 at 08:12

2 Answers2

1

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.

enter image description here

Formula:

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

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

enter image description here

You can create a custom Error message

enter image description here

enter image description here

WeAreOne
  • 1,310
  • 1
  • 2
  • 13
0

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

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.

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • Thanks for the reply. I tried substitute formula and replacing where the result is displayed in B column. I want the user to enter the number in A1 and converted in the same cell A1. – Anand Babu Dec 14 '22 at 09:55