Excel VBA - Find and Expand matching cell entries

RabbitWarren Posts 1 Registration date Tuesday May 2, 2017 Status Member Last seen May 2, 2017 - May 2, 2017 at 01:36 AM
 x - May 2, 2017 at 11:42 AM

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.

1 response

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

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

Result 0001234567

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