Excel VBA - Find and Expand matching cell entries [Solved/Closed]

Report
Posts
1
Registration date
Tuesday May 2, 2017
Status
Member
Last seen
May 2, 2017
-
 x -
Hello

I have a list of 10 digit numbers in each row of a column range. Some of the numbers start with 3 zeros (000) which results in Excel ignoring them. The numbers then becomes only 7 digit numbers.

I need some VBA code that will loop through the column and locate these 7 digit numbers then add '000 back as a suffix e.g: was 0001112345 > excel ignores zeros so becomes 1112345 > macro to add zeros back '0001112345 (needs the ' to make it text to retain the zeros).

The amount of rows that contains a number will vary so will need to include last Row Col to always stay within the changing range.

I have no idea how do this at all so cannot even start to provide some code to work with.

Can someone please help.

2 replies

You dont need VBA code to do this.
Add a new column and use the TEXT function.

e.g.
Cell A1: 1234567
Cell B1: =TEXT(A1,"0000000000")

Result 0001234567

then copy and paste-special-values from B1 into A1. remove B1