add 2 zeros to the beginning of each number

**garymaule** · 09-13-2011, 05:00 AM

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

**Azam Ali** · 09-13-2011, 05:38 AM

in b1 use
="00"&A1

and copy down, then paste values from column B to column A

see attatchment

**Azam Ali** · 09-13-2011, 05:52 AM

also in format cell use

General;General;General;"00"General

see attatchment

**garymaule** · 09-13-2011, 09:59 AM

got it working guys thanks for taking time out to help its appreciated

**Azam Ali** · 09-13-2011, 11:45 PM

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.