How format text field to have 14 characters

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
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Martin LissOlder than dirtCommented:
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

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

tomfarrarCommented:
Or you could do this:

=REPT(0,14-LEN(B3))&B3, assuming your first text field was B3; then copy down.  - Tom
0
Martin LissOlder than dirtCommented:
@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.
0
SteveL13Author 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.

??
0
Martin LissOlder than dirtCommented:
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.
0
SteveL13Author 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?
0
Rob HensonFinance AnalystCommented:
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
0
SteveL13Author 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,
0
Martin LissOlder than dirtCommented:
It works for me. Try the attached. I added a bit of code to ignore blank cells.
28713138.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
It did work.  Thanks.
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.