asked on

Excel issue data validation

I have a very large excel spreadsheet.

I have a UID column and the fields are always a number with 13 characters.

The 13 characters always start with two zeros (0012345678911)

However, there are fields within that column where the two zeros have been left out, which means there are only 11 characters i.e 12345678911

The two zeros are needed so that we can run a query in another database, otherwise the query fails. How can I add two zeros to all the fields that only have 11 characters and leave the fields with two zeros already there (13 characters)?

Saurabh Singh Teotia

Assuming you have data in A1, then in B1 you can use this formula.
=TEXT(A1,"0000000000000")
This will do what you are looking for.
Saurabh...

ASKER CERTIFIED SOLUTION

RichardSchollar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Bottomley

Place a macro in a module and then run it using alt + F8 having highlighted the rows/columns of interest

Chris

Sub make13()
Dim rng As Range
Dim cel As Range
 
    Set rng = Intersect(Application.Selection, Application.ActiveSheet.UsedRange)
    rng.Cells.NumberFormat = "@"
    For Each cel In rng
        If cel <> "" Then cel = String(13 - Len(cel), "0") & cel.Text
    Next
 
End Sub

Open in new window

siasupport

ASKER

Genius.

Gave the exact solution I was looking for, and made it very easy to understand by showing how to compltete the solution step by step.

zorvek (Kevin Jones)

Nicely done Richard!

:-)

Kevin