How do i append a number with "000"

[Talon]

I have over 5000 numbers in a single column that i need to append with triple
0 at the beginning of each, an example is below:

5086870

need it to be

0005086870


any ideas on how to add the "000" prefix to every one of the numbers in a
column??

TIA and cheers! Talon

 

[bigwheel]

Give your cells a custom format of 10 0's

 

[Talon]

It worked like a charm, thanks!!!!!

Give your cells a custom format of 10 0's

 

[Nick Hodge]

Talon

Use a helper column and enter

="000"&A1

Where A1 is the original data and copy down

Copy this new data and the edit paste special+Values over the old data, you
may need to pre-format the original column as text to get it to stick

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Nick Hodge]

Talon

Do remember that formatting is only a 'mask'. The underlying data will still
be 5086870 so you will need to be sure that's what you use on any LOOKUPs or
similar

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Barb Reinhardt]

Try using a custom format for the cells of 0000000000

 

[Prasadkakarla]

try this

="000"&B3


u will get second col