How format text field to have 14 characters

Here is a macro

Sub make14()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row
For lngRow = 1 To lngLastRow
    Cells(lngRow, "A") = Format(Cells(lngRow, "A"), "00000000000000")
Next
End Sub

Select all Open in new window

You can apply custom formatting on those cells.
Select your cells --> Right Click --> Select Format Cells --> From Number Tab select Custom --> Now in Type box input 14 0's i.e. 00000000000000 and click on OK.

My first post was wrong.

Sub make14()
Dim lngLastRow As Long
Dim lngRow As Long
Const ZEROS = "00000000000000"

lngLastRow = Range("A1048576").End(xlUp).Row
For lngRow = 1 To lngLastRow
    Cells(lngRow, "A") = Mid(ZEROS, 1, 14 - Len(Cells(lngRow, "A"))) & Cells(lngRow, "A")
Next
End Sub

Select all Open in new window

Or you could do this:

=REPT(0,14-LEN(B3))&B3, assuming your first text field was B3; then copy down.  - Tom

@SteveL13: I don't know which one you want, but the difference between my second post and sktneer's is that mine actually changes the value whereas his just changes the appearance.

sktneer:  I am trying your approach but when I select what appears to be the converted cell, it still only displays the original value without the preceeding zeros.

??

My post ID: 40973007 does what you want.

Just in case you need them, here are instructions on how to install and use the macro.

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window). If you don’t see an existing module then select Insert -> Module from the context menu. Otherwise just select the module.

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+A.

When you close the workbook you will need to save it as an xlsm or xls file if it’s not already one of those.

Martin Liss:  I am trying your approach but it only changes the data in A1.  I really want it to change the data in A2 through A4396.  How would I alter the macro?

Alternative:

=TEXT(A1*1,"00000000000000")

Where first value is in A1, copy down as required and copy paste values over originals.  The multiplication by 1 allows for the cell being already text and forces convert to number before converting back to text.

Thanks
Rob H

Martin Liss:  I am trying your approach but it only changes the data in A1.  I really want it to change the data in A2 through A4396.  How would I alter the macro?

I can't figure out how to change the range,

It did work.  Thanks.

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014