Quick Excel help please!!

Totality · 28 Sep 2007 at 13:22

This could be in the wrong forum but its a quick question on formatting in Excel (Excel 2007 if that makes a difference).

Basically I have a column with 500 rows of numbers in the format - 12345/1

I need them in the format 00012345/00001

How can I add in the extra 000's? Or make the cell automatically put 8 digits in front of the / and 5 after adding in the extra 0's automatically?

I'm useless at this sort of stuff and dont want to waste hours doing it all manually!

Thanks

melbourne720 · 28 Sep 2007 at 13:30

Enter the value in the cell as =12345/1

Format Cells, Custom.

Enter the formula as 0000#####/0000#####

Click OK.

:cool:

Totality · 28 Sep 2007 at 13:37

Cheers. Looked promising but doesnt work

I hate Excel!!!

melbourne720 · 28 Sep 2007 at 13:44

working for me. Send me the spreadsheet (or a subsection) and I'll fix it for you. E-mail in trust. Don't forget save it as .xls format.

PMKeates · 28 Sep 2007 at 14:12

If you have no luck you can use the formula below to convert data, replacing A1 with the relevant cell reference.

="000"&LEFT(A1,5)&"/0000"&RIGHT(A1,1)

Totality · 28 Sep 2007 at 14:20

Cheers

That formula sorted it. Must be something to do with Excel 2007 :confused:

EDIT: AWw now when I copy the fixed cells it copies the formulas rather than the values!! ARGH!!!

EDIT 2: Paste special F T W

All done cheers!