Excel VBA Tutorial - Removing "Dashes" from I/C Number

 

 

You may use Excel VBA to remove dashes in I/C numbers, and maintain the format without changing to Scientific Format. Here is the data:

and here is the result that you would like to get

Steps to achieve the above:

1. Press Alt-F11 to open the Visual Basic Editor, and enter the following code: Sub DashReplacement() Selection.NumberFormat = "00000000000" Selection.

Replace What:="-", Replacement:="" End Sub 

 

2. Highlight the range that contains the I/C numbers that you would like to work on 

 

3. Return to VBA editor and execute the DashReplacement by pressing F5

 

This will be useful if you want to remove extra dashes, or unwanted characters that may appears in your Product No or Employee No.

Feel free to amend the code to suit your needs.

 

By Liang Ee Hang  | Google  | LinkedIn

 

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955
Instant SSL