Report

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

Ask a question RabbitWarren 1Posts Tuesday May 2, 2017Registration date May 2, 2017 Last seen - Last answered on May 2, 2017 at 11:42 AM by 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.
Helpful
+0
plus moins
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
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!