Shows only number on entered value in a cell

Question

I write "WWW 12345" (or "WWW 12345" or "WWW12345" or "www 12345") in a cell. When I press enter (or arrows), I want to see in a cell only digits (e.g 12345).

In vba, I tried with Workbook_SelectionChange, but doesn't works. I tried to create a custom format cell, but it is available only for digits.

How can I make this? I'm not programmer, so I try to find a simple solution (that I can understand it).

Solution presented [here](http://stackoverflow.com/questions/7239328/how-to-find-numbers-from-a-string) should work — Michael Russo, Aug 10 '16 at 14:55
You can also use [string manipulation functions in Excel VBA](http://www.excel-easy.com/vba/string-manipulation.html) — Anastasiya-Romanova 秀, Aug 10 '16 at 14:57
@ThomasInzina: no, I type "WWW 12345" and when I press enter (or arrow) to navigate to another cell, I need to text change in "12345" — colombo2003, Aug 10 '16 at 15:12
@Michael Russo: it works, but... only if I reselect the cell, or I need on leave cell (press enter or arrows) the text changes — colombo2003, Aug 10 '16 at 15:17
Why not just use Excel's build in data validation and allow "Whole number"? If you don't care about the alpha characters entered, just dis-allow it from the start. — sous2817, Aug 10 '16 at 15:44

score 1 · Accepted Answer · answered Aug 10 '16 at 15:37

1

Use this in conjunction with the onlyDigits function or regex function from link.

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Value = onlyDigits(Target.Value)
End Sub

You could add a check to make sure this is only called when data in a certain column is change vs the entire sheet.

answered Aug 10 '16 at 15:37

Michael Russo

442
6
14

Update: when I want to delete a entered value, MS Excel has stopped working and close it. "A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available". I tried to put this line "If Len(s) > 0 Then" (and End if to end) before For line in the onltDigits function from link, but doesn't work. How can I fix that? Please help! Thx. – colombo2003 Aug 11 '16 at 11:40
Try putting the condition in the `Worksheet_Change` using Target.Value. – Michael Russo Aug 11 '16 at 12:12
SOLVED! But new I have another little problem! I want to validate the entered values to prevent duplicated. "www123" is converted in "123" (which is ok), but when I entered "123" nothing happened (I would like to see the message set in Data Validate) – colombo2003 Aug 11 '16 at 12:46
Well after your null check, you could grab the converted value, loop through the cells to see if it already exists, if not let it set `Target.Value`. Otherwise you can use a message box to show the user that the value exists already. – Michael Russo Aug 11 '16 at 12:50

Shows only number on entered value in a cell

1 Answers1