Avatar of SteveL13
SteveL13
Flag for United States of America asked on

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
Microsoft ExcelVBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

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)

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 Liss

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Tom Farrar

Or you could do this:

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

@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.
SteveL13

ASKER
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.

??
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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.
SteveL13

ASKER
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 Henson

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SteveL13

ASKER
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,
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
It did work.  Thanks.
Martin Liss

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.