I need to format a single column of numbers to always have 4 digits. If the cell contents is less than 4 digits, then I need it to concatenate with preceding zeros to bring the total number of digits always to 4.
so...
3205 is ok
905 is not ok....need it to be 0905.
I need to concatentate vs converting to text so the value includes the zeros. These numbers are constantly being input via web query, so a formula in the cell will get wiped out. Possibly need a vba macro to run after the query has finished.
Thanks for any help.
Concatenate with leading zeros
-
-
-
Re: Concatenate with leading zeros
A quick way to format the cells in the column would be to select the column (or the parts you want to format eg, if there is a header row) and use a custom number format.
Take a look here for more information: http://support.microsoft.com/kb/81518 -
Re: Concatenate with leading zeros
yes, that is a good way to have the values APPEAR with the correct number of digits, however if you are manipulating the numbers they will only have what they were input with. I am using a LEFT function down the road with these numbers...it needs to have 4 digits..always....not just appear to have 4 digits.
-
Re: Concatenate with leading zeros
The result will be text, not a number, but the formula =TEXT(A1,"0000") will do what you want.
Note, that the number 0123 is exactly the same as the number 123 so no number has "leading zeros", only numerals (text strings that represent numbers) have leading 0's.
-
Re: Concatenate with leading zeros
As mikerickson has said, you will need to convert these numbers to Text values. If you want to do this via a macro rather than messing around with inserting columns in your sheet, highlight the column (or part of the column) you want to modify and run the SelectionPadLeft macro below:
Code
Display MoreOption Explicit 'This function will left pad a string with a specified character 'if fStrict is TRUE then the resultant string will be truncated to iPadLen Private Function LeftPad(ByVal sValue As String, ByVal iPadLen As Integer, _ Optional ByVal sPadChar As String = "0", _ Optional ByVal fStrict As Boolean = True) Dim PadLength As Integer, i As Integer Dim PadString As String PadLength = iPadLen - Len(sValue) PadString = vbNullString For i = 1 To PadLength PadString = PadString & sPadChar Next LeftPad = PadString + sValue If fStrict Then LeftPad = Right(LeftPad, iPadLen) End Function Sub SelectionPadLeft() Dim cCell As Range For Each cCell In Selection If IsNumeric(cCell.Value) Then With cCell .NumberFormat = "@" .Value = LeftPad(cCell.Value, 4) .NumberFormat = "General" End With End If Next End Sub
This code will only modify cell values that are numeric to begin with.
-
-
Re: Concatenate with leading zeros
Works perfect. Thanks!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!