insert zeros in front of cells already filled with data.

If there is data is a column of cells in Excel how do I insert/add additional 
data (000) in front of each cell?
0
Horizon (1)
1/16/2006 8:31:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
604 Views

Similar Articles

[PageSpeed] 26

Two ways. If all cells should be the same length, like a check number or an 
account number, it's called "leading zeroes":
http://www.officearticles.com/excel/show_leading_zeroes_in_microsoft_excel.htm
If you just want to put 3 zeroes in front of other text, you can use another 
column to create a formula:
="000"&a1
will give you the contents of A1 preceded by 3 zeroes.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Horizon" <Horizon@discussions.microsoft.com> wrote in message 
news:38BBD536-B411-4EE7-9D52-6B7217632376@microsoft.com...
> If there is data is a column of cells in Excel how do I insert/add 
> additional
> data (000) in front of each cell? 


0
ng1 (1444)
1/16/2006 8:41:08 PM
Insert a column before it, and fill it with "'000", don't forget '
Then concatenate those two rows, that should do it.
You can copy paste values and delete the other two columns to get it
back to the same way it was before.  If you click into it will take
your 0s away, not fair I know.  You won't have that problem if you keep
the formulas there.

Hope that help,

Chris


-- 
cbh35711
------------------------------------------------------------------------
cbh35711's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30276
View this thread: http://www.excelforum.com/showthread.php?threadid=501802

0
1/16/2006 8:47:18 PM
Reply:

Similar Artilces:

format cell to show zero if not a positive number
How can I format a column of figures to show that if the result is zero or negative amount it will just show a zero or be left blank. I am attempting to show the difference between figures that have been invoiced to figures that have been added and thus need to be invoiced. Use a custom format like #,##0.00_);; or #,##0.00_);"0";"0" HTH, Bernie MS Excel MVP "TechnoGram" <mumzee3@hotmail.com> wrote in message news:4183a06c-5eb1-4699-9efd-fd8a4bdd4e07@d62g2000hsf.googlegroups.com... > How can I format a column of figures to show that if the result i...

Looking for cells containing numbers
Hi, in a column with cells containing either text or numbers I have to exclude those with text in an arithmetic formula as is runs through the whole column, hence avoiding an error as a result when reading text cells. A cloumn holding an intermediate result of the reading as "0" or "1" or something will be just fine. How to? Cheers, Thorkild You might be thinking of something like this ? Assuming the col with text/numbers is col A, A1 down Put in B1: =IF(ISTEXT(A1),0,A1) Copy B1 down Then use col B instead of col A for downstream calcs -- Rgds Max xl 97 --- GMT+8, ...

Count with Hidden cells
Is there a way to count the number of text filled cells excluding the cells that I have hidden? Depends on the version of excel, excel 2003 works like =SUBTOTAL(103,A1:A10) won't count hidden cells -- Regards, Peo Sjoblom (No private emails please) "Stretch" <Stretch@discussions.microsoft.com> wrote in message news:9F387E47-FC1F-4AEB-8A6C-2B37BC012DD1@microsoft.com... > Is there a way to count the number of text filled cells excluding the > cells > that I have hidden? What does the 103 stand for? "Peo Sjoblom" wrote: > Depends on the v...

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Copying and pasting a series of cells
When I copy and paste the following cell > =IF('13'!B13>0,'13'!B13,NA()) to the remainder of the days of the month, what I get is > =IF('13'!B14>0,'13'!B14,NA()) > =IF('13'!B15>0,'13'!B15,NA()) > etc But what I want to get is > =IF('14'!B13>0,'14'!B13,NA()) > =IF('15'!B13>0,'15'!B13,NA()) > etc I'd even settle for > =IF('13'!B13>0,'13'!B13,NA()) > =IF('13'!b13>0,'13'!B13,NA()) > etc whereafter I could just modify the fo...

Spliiting a cell
I have a column and its contents is a question which states... What is the Cost of Superbills? Quantity of: I would like to split this lone cell and have the quantity right next to the question. Can I do this??? Make sure the column to the right is empty, then do data>text to columns, select fixed width, click next and click where you want the split. If there can be different strings with different lenght you can use a formula to find for instance the question mark =TRIM(MID(A1,FIND("?",A1)+1,255)) Regards, Peo Sjoblom "RTP" wrote: > I have a column and it...

Not plot a data series in a chart but still display in data table
MS Excel 2007 I've somehow managed to display only two data series on my line chart. They still display in the data table. How did this happen? More importantly, how do I reverse it. I don't want to recreate chart. I see the data series not plotted on chart in the Chart Elements and when I select the series, the source data is highlighted; it's in the data table. How do I make it visible? Thanks! Miriam HAve you perhaps simply formatted the data series as "no line, no marker"? If so, you can format series, add some color. -- Best Regards, Lu...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

OWA 2003 Front End logout error
Only on my OWA 2003 front end server do I get this error: If you click the Logout button in OWA I get the login box again. If you click cancel it gives you a blank page with the error "Access is denied, but if you use the Back button in your browser you are still logged in. All three of me Exchange servers are 2003 on Windows 2003. If I logon to OWA directly to non-Front End servers the logout button works correctly. It's only on my front end server and happens using both http or https. Thanks for any advice. Make sure that on that FE server, you have anonymous set on the Exch...

Need fast way to move from cell to home
What is the quickest way to move from a cell to home (A1) in Excel? I thought you could click on something or hold down two keys in order to accomplish this, just can't remember. Thanks. CTRL+HOME -- Kind Regards, Niek Otten Microsoft MVP - Excel "Debbie" <Debbie@discussions.microsoft.com> wrote in message news:B5A4892A-F180-47FA-A9F0-F91BA3CAFA64@microsoft.com... > What is the quickest way to move from a cell to home (A1) in Excel? I > thought you could click on something or hold down two keys in order to > accomplish this, just can't remember. T...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Worksheet name in cell formula
I asked this question a while ago and got a prompt answer which I thought was what I wanted but alas its not... I want to be able to change my worksheet names.....ie: from Sheet 1, Sheet 2, etc etc.....to something more meaningful.....eg Sales, Expenses....etc etc.... and have these changes reflect on the worksheet. For example, I might want Sheet 1 Cell A1 to say.....This is the Sales worksheet (assuming I have renamed it to Sales). The answer I was given some time back worked.....but it changed the formula on every worksheet to reflect the name of the last changed sheet. By this I mean.....

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Buying a mutual fund with a 5.75% front end load.
Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take the purchase price time 5.75% and put that in commission. Or, do I jack up the purchase price by 5.75% and then take that amount back out in commission? Thanks in advance, Harold Morgan In microsoft.public.money, Harold Morgan wrote: >Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take >the purchase price time 5.75% and put that in commission. Or, do I jack up >the purchase price by 5.75% and then take that amount back out in >commission? I would enter the numb...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

VLOOKUP and cell color problem.
I have a spreadsheet to create a quotation/estimate and I use the VLOOKUP function to retrieve the price of an item on another worksheet. In that worksheet, I have several pricelists from various suppliers but one column gives me the lowest price and changes the cell color associated with the supplier so I know from which supplier the price comes from. Now my main quotation page displays the cheapest price but does not pass along the cell color which I need so i can tell, by looking at the quotation, which supplier i need to order each item from. Is there a way to pass along not only the va...

Split a cell
Am I able to split a cell, making it 2 as in Microsoft Word? Select a cell or entire column you want to split, then select Data - Text to Columns. Make sure you have an empty column on the right of the column you're spliting or there won't be any room for the data you're spliting off. >-----Original Message----- >Am I able to split a cell, making it 2 as in Microsoft >Word? >. > Priscilla, No. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Priscilla" <anonymous@discussions.microsoft.c...

Not plot zero on line chart without using NA?
Is there any way to have a line chart ignore the zeros other than manipulate the formula to show NA? If there were, would we settle for using NA()? We've been asking for something better for several versions of Excel. There are two options: use NA() in the formula, or delete the formula altogether. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Homer wrote: > Is there any way to have a line chart ignore the zeros other than manipulate > the formula to show NA? > > That's what I thought, but I figured I'd take a shot at ...

showing data values on chart
in excel when mouse stand on chart,excel is showing the data values of that point.how can to do this action in access? Exactly the same. When you hover the mouse over a data point for a little amount of time, a yellow bow should come up describing what's in your data point. -- Regards, Steve "afshin" wrote: > in excel when mouse stand on chart,excel is showing the data values of that > point.how can to do this action in access? Where you insert chart?i insert chart on report,and i can't to see data value on data point. "Steve"...

How to copy specific columns until end of data
I'm trying to import data from 2 workbooks into a summary (combination) workbook. The trouble I'm having, is I want to import only certain columns and have excel know to stop at the last row with data. For example, from Workbook1 I want to import columns A, B, C, F, and G (from Sheet2). I want it to copy up to the last row containing any data--right now that's row 1046, but next month it could be row 1267. In workbook2 I'm fine with it copying the CurrentRegion--all columns are important. BUT, it doesn't include the title/category heads in the first row (Salesperson, ...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Uploaded Checking acct data
Using 2005 Just successfully uploaded all 2004 (YTD) for main checking account. That said, why does the calender not reflect the payments? My calendar shows nothing just "Add New" Is there some steps that I need to do or is the calendar information updated only if I, step-by-step, pay bills in the system? TIA Dennis The data that you have uploaded will be in the account register, not in the bills/deposits area which populates the calendar. To get it in the calendar, you'll need to add it to the bills/deposits area. You can do this by either entering it manually or by...