How format text field to have 14 characters

SteveL13
SteveL13 used Ask the Experts™
on
I have an Excel file that has a column of cells that are formatted as text.  This is a little confusing in that the characters are actually numbers but the column needs to remain formatted as text.

My issue however is that some of the cells contain 2 characters, others contain 6, 7, 8, 9 characters, etc.

I need to somehow make them 14 characters in length with zeroes in front of whatever is there already.  So for example, 14 would become 00000000000014.  Or another example would be 5623 would be 00000000005623, and so forth.

How can I convert these strings of character to include the zeroes in front of whatever is there already?

--Steve
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Or you could do this:

=REPT(0,14-LEN(B3))&B3, assuming your first text field was B3; then copy down.  - Tom
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
@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.

Author

Commented:
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.

??
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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?
Rob HensonFinance Analyst

Commented:
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

Author

Commented:
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,
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
It works for me. Try the attached. I added a bit of code to ignore blank cells.
28713138.xlsm

Author

Commented:
It did work.  Thanks.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial