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

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

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

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

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