Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Please help.
I have a large spreadsheet to format. The first 3 columns contain parts of an account number. The first column containing 2 digits (00) the 2nd containing 5 digits (00000) and the 3rd containing three digits (000) ie. 00 00000 000. I have used the following formula to migrate the contents into 1 column, =(A3&" "&B3&" "&C3) in the hope that it would form a number like this 00 00000 000. The problem is some of the account numbers begin with zeros. so 01 00435 020 shows as 1 435 20. I have formatted the cells to show a custom number, pasted the values rather than the formula in the cells and nothing will work. Any help would be very much appreciated. Thanks -- RMP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
=(TEXT(A3,"00")&" "&TEXT(B3,"00000")&" "&TEXT(C3,"000"))
-- David Biddulph "RMP" wrote in message ... Please help. I have a large spreadsheet to format. The first 3 columns contain parts of an account number. The first column containing 2 digits (00) the 2nd containing 5 digits (00000) and the 3rd containing three digits (000) ie. 00 00000 000. I have used the following formula to migrate the contents into 1 column, =(A3&" "&B3&" "&C3) in the hope that it would form a number like this 00 00000 000. The problem is some of the account numbers begin with zeros. so 01 00435 020 shows as 1 435 20. I have formatted the cells to show a custom number, pasted the values rather than the formula in the cells and nothing will work. Any help would be very much appreciated. Thanks -- RMP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Try =CONCATENATE(TEXT(A3,"00")," ",TEXT(B3,"00000")," ",TEXT(C3,"000"))
-- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "RMP" wrote: Please help. I have a large spreadsheet to format. The first 3 columns contain parts of an account number. The first column containing 2 digits (00) the 2nd containing 5 digits (00000) and the 3rd containing three digits (000) ie. 00 00000 000. I have used the following formula to migrate the contents into 1 column, =(A3&" "&B3&" "&C3) in the hope that it would form a number like this 00 00000 000. The problem is some of the account numbers begin with zeros. so 01 00435 020 shows as 1 435 20. I have formatted the cells to show a custom number, pasted the values rather than the formula in the cells and nothing will work. Any help would be very much appreciated. Thanks -- RMP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Thank you very much, you have no idea how much easier you just made my life!!!!
Good job! -- RMP "David Biddulph" wrote: =(TEXT(A3,"00")&" "&TEXT(B3,"00000")&" "&TEXT(C3,"000")) -- David Biddulph "RMP" wrote in message ... Please help. I have a large spreadsheet to format. The first 3 columns contain parts of an account number. The first column containing 2 digits (00) the 2nd containing 5 digits (00000) and the 3rd containing three digits (000) ie. 00 00000 000. I have used the following formula to migrate the contents into 1 column, =(A3&" "&B3&" "&C3) in the hope that it would form a number like this 00 00000 000. The problem is some of the account numbers begin with zeros. so 01 00435 020 shows as 1 435 20. I have formatted the cells to show a custom number, pasted the values rather than the formula in the cells and nothing will work. Any help would be very much appreciated. Thanks -- RMP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Thank you very much, that is awesome. Made everything a hell of a lot easier!!
-- RMP "john" wrote: Try =CONCATENATE(TEXT(A3,"00")," ",TEXT(B3,"00000")," ",TEXT(C3,"000")) -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "RMP" wrote: Please help. I have a large spreadsheet to format. The first 3 columns contain parts of an account number. The first column containing 2 digits (00) the 2nd containing 5 digits (00000) and the 3rd containing three digits (000) ie. 00 00000 000. I have used the following formula to migrate the contents into 1 column, =(A3&" "&B3&" "&C3) in the hope that it would form a number like this 00 00000 000. The problem is some of the account numbers begin with zeros. so 01 00435 020 shows as 1 435 20. I have formatted the cells to show a custom number, pasted the values rather than the formula in the cells and nothing will work. Any help would be very much appreciated. Thanks -- RMP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number formatting | Excel Worksheet Functions | |||
number formatting | Excel Worksheet Functions | |||
formatting cell number based on previous cell number | Excel Discussion (Misc queries) | |||
Number formatting | Excel Discussion (Misc queries) | |||
Number formatting | Excel Worksheet Functions |