how do I add a zero in front of a bunch of 4 digit numbers?

[Guest]

I have a spreadsheet with over 1000 CSI codes that are only 4 numbers. The 4
numbered CSI codes all need a Zero in front to make it a five digit code. Is
there an easy way to just globally add a zero to just these 4 digit codes?
Example:
Wrong codes How I want them to look
2515 02515
2351 02351
7524 07524

Just hoping there is a way to avoid having to go into each cell and add a
zero.
Thanks for your help

 

[Bob Phillips]

Format>Cells>Custom, and enter a value of 00000

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

use =CONCATENATE("0",A2) in proper columns and copy it down , ofcourse you
need to change A2 to a desired column.

 

[www.exciter.gr: Custom Excel Applications!]

Suppose you 4-digit number are in Column A, starting from row 1. You
could insert the following formula in cell B1 and drag to final row:
="0" & A1

This way your cells will actually have a leading zero. If you just
format the column, the leading zero is only visible in cell and not
actually inserted before your numbers. If you only want to view
leading zero, prefer Bob's suggestion, if you want to manipulate your
cells in other formulas and want them to have the leading zero, prefer
the above formula.

Best.

http://www.exciter.gr
Custom Excel Applications and Functions!

 

[JP]

If your data is in column A, starting in A1, you could enter this
formula in B1

="'0"&A1

and fill down. But that would change your numbers to text.

Or you could format the column as text and use the formula ="0"&A1


HTH,
JP

 

[Guest]

Hi all.
For several lenghts of datas, try:
=REPT(0,5-LEN(A2)) & A2
Regards
Eliano

 

[Guest]

Hi Jenilise.
For 1000 codes and over i believe is bettere a Vba code; try:

Public Sub prova()

Dim L As Long
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range, rCell As Range
Dim LRow As Long
Dim LV As Long

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Foglio2")
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = SH.Range("A2:A" & LRow)
Rng.NumberFormat = "@"
LV = 5
For Each rCell In Rng.Cells
rCell.Select
If Len(rCell) < LV Then
L = Len(rCell)
For L = Len(rCell) To LV - 1
rCell = "0" & rCell
Next L
End If
Next rCell
End Sub

Regards
Eliano

 

[Guest]

Thank you so much Bob... worked perfect!

Format>Cells>Custom, and enter a value of 00000

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)