add 2 zeros to the beginning of each number

[garymaule]

Hello guys

anyone able to help out with a small problem i have
i have a whole row of numbers that contain a letter in each line e.g

3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1
3478L1
1357L1

what i would like to do is add 2 zeros to the beginning of each number eg

003478L1
001257L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1
003478L1
001357L1

guys i have tried right clicking on A1 the selecting format cells then number then custom and entering 000000
this works ok for a standard 6 digit number but mines contains a letter L and thus cause this formula to fail

does anyone have have any ideas how i would get 2 zeros to the start of each number please

many thanks

Gary

 

[Richard Schollar]

Hi Gary

Use a formula such as:

="00"&A2

in an adjacent column to generate the desired rsults, then copy this formula column and pastespecial>values over your orginals (and delete the formulas).

 

[VoG]

With a helper column, formula in B1 copied down

Excel Workbook
	A	B
1	3478L1	003478L1
2	1357L1	001357L1
3	3478L1	003478L1
4	1357L1	001357L1
5	3478L1	003478L1
6	1357L1	001357L1
7	3478L1	003478L1
Sheet4

 

[garymaule]

hello guys

thankyou for the replies


this looks ideal........... could you give me some basic step by step instructions please hot to work this formula

sorry i'm not all that good with excell

thankyou for taking time out to help its appreciated


Sheet4


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset">A</TD><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset">B</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">1</TD><TD>3478L1</TD><TD>003478L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">2</TD><TD>1357L1</TD><TD>001357L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">3</TD><TD>3478L1</TD><TD>003478L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">4</TD><TD>1357L1</TD><TD>001357L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">5</TD><TD>3478L1</TD><TD>003478L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">6</TD><TD>1357L1</TD><TD>001357L1</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-LEFT-COLOR: #f0f0f0; BORDER-BOTTOM-COLOR: #f0f0f0; BORDER-TOP-STYLE: outset; BORDER-TOP-COLOR: #f0f0f0; BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-BOTTOM-STYLE: outset">7</TD><TD>3478L1</TD><TD>003478L1</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>="00"&A1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[VoG]

In B1 enter

="00"&A1

Click in B1, hover the cursor over the bottom right corner of the cell until the cursor turns into a + then double click to copy the formula down.

 

[garymaule]

got it guys a big thankyou for all your help