Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default adding extra digits to columns

I have a column of numbers (isbn for books) which should all have 10 digits.
Somewhere in the copying process some of the numbers have not been formatted
as text and have dropped the zero from the beginning of the number. I need to
add a zero to the beginning of all the nine digit numbers in the column. How
do I achieve this ?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 179
Default adding extra digits to columns

if you want just display 10 digit: go format cell-Custome-Enter
"0000000000" to Type box
if you want to convert as text format and 10 digit:
A1: your value
B1=RIGHT("000000000"&A1,10)


"Muhammed Rafeek M" wrote:

if you want just display 10 digit: go format cell-Custome-Enter
"0000000000" to Type box
if you want to convert as text format and 10 digit:
A1: your value
B1=RIGHT("000000000"&A1,8)



"Graham at Mac's" wrote:

I have a column of numbers (isbn for books) which should all have 10 digits.
Somewhere in the copying process some of the numbers have not been formatted
as text and have dropped the zero from the beginning of the number. I need to
add a zero to the beginning of all the nine digit numbers in the column. How
do I achieve this ?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default adding extra digits to columns

If values can be of length 9 or 10 try:

=IF(LEN(A2)=10,TEXT(A2,"@"),CONCATENATE("0",A2))

then copy/paste special=values.

HTH

"Muhammed Rafeek M" wrote:

if you want just display 10 digit: go format cell-Custome-Enter
"0000000000" to Type box
if you want to convert as text format and 10 digit:
A1: your value
B1=RIGHT("000000000"&A1,10)


"Muhammed Rafeek M" wrote:

if you want just display 10 digit: go format cell-Custome-Enter
"0000000000" to Type box
if you want to convert as text format and 10 digit:
A1: your value
B1=RIGHT("000000000"&A1,8)



"Graham at Mac's" wrote:

I have a column of numbers (isbn for books) which should all have 10 digits.
Somewhere in the copying process some of the numbers have not been formatted
as text and have dropped the zero from the beginning of the number. I need to
add a zero to the beginning of all the nine digit numbers in the column. How
do I achieve this ?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup - effect of adding columns Paul Excel Discussion (Misc queries) 5 April 16th 08 07:39 AM
Adding columns (one with a formula) PaulStroik New Users to Excel 7 June 27th 05 10:32 PM
Concatenate function - keeping "displayed" extra digits EricKei Excel Discussion (Misc queries) 3 June 15th 05 10:16 PM
how do i delete the extra empty rows and columns that i dont need. lindaY Excel Discussion (Misc queries) 3 March 20th 05 04:48 PM
adding columns Garry Excel Discussion (Misc queries) 1 March 17th 05 11:20 PM


All times are GMT +1. The time now is 04:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"