Replace leading zeros with leading spaces ?

I am using Excel 2007.
Format (12,”000”) gives me 012
I want (12,”??0”) to be  12 with a leading space

Question mark doesn’t seem to work in Excel 2007

0
Utf
1/6/2010 8:07:02 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1010 Views

Similar Articles

[PageSpeed] 54

Use this...

Format(12, "@@@")

-- 
Rick (MVP - Excel)


"Gary" <Gary@discussions.microsoft.com> wrote in message 
news:27CFD9C0-FA9C-4A49-8557-3B86FBA0CD93@microsoft.com...
>I am using Excel 2007.
> Format (12,”000”) gives me 012
> I want (12,”??0”) to be  12 with a leading space
>
> Question mark doesn’t seem to work in Excel 2007
> 

0
Rick
1/6/2010 8:28:53 PM
I don't think you need to use Format.

So you want "12" to look like " 12"?

MyString = " 12"

But I know if can't be that simple.  Is "12" a value in a cell?  If so, use 
this:

MyString = " " & Range("A1").Value
-- 
Cheers,
Ryan


"Gary" wrote:

> I am using Excel 2007.
> Format (12,”000”) gives me 012
> I want (12,”??0”) to be  12 with a leading space
> 
> Question mark doesn’t seem to work in Excel 2007
> 
0
Utf
1/6/2010 8:37:02 PM
I need Format because this is part of a loop building a dynamic Message Box 
that has several columns; I need the columns to be aligned for readability.

Gary

"Ryan H" wrote:

> I don't think you need to use Format.
> 
> So you want "12" to look like " 12"?
> 
> MyString = " 12"
> 
> But I know if can't be that simple.  Is "12" a value in a cell?  If so, use 
> this:
> 
> MyString = " " & Range("A1").Value
> -- 
> Cheers,
> Ryan
> 
> 
> "Gary" wrote:
> 
> > I am using Excel 2007.
> > Format (12,”000”) gives me 012
> > I want (12,”??0”) to be  12 with a leading space
> > 
> > Question mark doesn’t seem to work in Excel 2007
> > 
0
Utf
1/6/2010 8:59:01 PM
YES !!!  That works .

Thanks,

Gary

"Rick Rothstein" wrote:

> Use this...
> 
> Format(12, "@@@")
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Gary" <Gary@discussions.microsoft.com> wrote in message 
> news:27CFD9C0-FA9C-4A49-8557-3B86FBA0CD93@microsoft.com...
> >I am using Excel 2007.
> > Format (12,”000”) gives me 012
> > I want (12,”??0”) to be  12 with a leading space
> >
> > Question mark doesn’t seem to work in Excel 2007
> > 
> 
> .
> 
0
Utf
1/6/2010 9:21:03 PM
You can also use

Val(your string here)

For example,

Val(0000012) = 12
-- 
Cheers,
Ryan


"Gary" wrote:

> I need Format because this is part of a loop building a dynamic Message Box 
> that has several columns; I need the columns to be aligned for readability.
> 
> Gary
> 
> "Ryan H" wrote:
> 
> > I don't think you need to use Format.
> > 
> > So you want "12" to look like " 12"?
> > 
> > MyString = " 12"
> > 
> > But I know if can't be that simple.  Is "12" a value in a cell?  If so, use 
> > this:
> > 
> > MyString = " " & Range("A1").Value
> > -- 
> > Cheers,
> > Ryan
> > 
> > 
> > "Gary" wrote:
> > 
> > > I am using Excel 2007.
> > > Format (12,”000”) gives me 012
> > > I want (12,”??0”) to be  12 with a leading space
> > > 
> > > Question mark doesn’t seem to work in Excel 2007
> > > 
0
Utf
1/7/2010 11:39:01 AM
Reply:

Similar Artilces:

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

eliminate spaces after a text field
Hello, I am designing in query and there are spaces after text field. How do I delete the spaces after? Thanks Try something like: NewField: Trim([YourField]) To remove trailing spaces only (not leading spaces): NewField: RTrim([YourField]) Spaces in the middle of the field will not be affected. Use your actual field name in place of YourField. Use whatever you like in place of NewField. "Cam" <Cam@discussions.microsoft.com> wrote in message news:5BB9EA84-D7F8-416C-B2C0-2155F02B8BBA@microsoft.com... > Hello, > > I am designing in query and there are spaces ...

Search and Replace in a column
I can't remember how I did this before and not having much success with my syntax. I just want to do a simple search and replace of all dashes in a field with a blank. Table = Vendor Column = PhoneNumber Basically: replace(PhoneNumber,'-','') I just want to strip the - (dash) from the field. What's the easiest way to do this? SQL Server 2005. Thanks, Greg You posted the correct syntax: SELECT REPLACE(PhoneNumber, '-', '') AS phone FROM Vendor; Or if you need to update: UPDATE Vendor SET PhoneNumber = REPLACE(P...

Find and Replace error
Hi. I have a worksheet where I want to show only some of the formulas used in it. That is why, instead of using the option "Show formulas" from the formulas Tab, I insert a ' before the = in the cells I want to see the formula. But when I want to show results once again, in order no to look for each formula in which I added the ', I use the Find and Replace Option. In Find I specify '= and leave in blank the Replace option. But Excel shows me the following message: "Excel cannot find the data you are searching for". Cannot figure out the co...

Zip Code Last 4 Digit as Zero
I have a Zip Code Table with only the first 5 digits and do not have the last 4 digit. How can I make them in 9 digit zip code with the last 4 digit as 0000?. manually I've been doing this (01234 then add the last digit as -0000) which come to this 01234-0000. With a five digit ZIP code in A1, in another cell: =A1 & "-0000" -- Gary''s Student - gsnu201003 "cheppy" wrote: > I have a Zip Code Table with only the first 5 digits and do not have the last > 4 digit. > How can I make them in 9 digit zip code with the last 4 digit...

Chart won't show zero percent
I am doing a pie chart using a quick style design that shows the lines in between the different percentages. Some of the charts show a percentage with the lines when the value is zero and two of my charts erase all but one of the lines when a zero is placed in one of the cells. What's up with that? and How do I correct it? Since with zero value the lines of the pie would be on top of each other, I am not sure what one would expect to see! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "PURVIANCE" <PURVIANCE...

Find and Replace #6
Hi, I have Excel 2002 (with Win 2000 ). 1. Lets say I have 10 cells with the text -> Apple in it. Suppose I choose 6 of these cells which has apple in it and then use the Find feature and type Apple and use Find All feature then I get a message in message bar at the bottom of Find saying 6 cells found. 2. Now, I close the find window.I select the 6 cells with Apple again and now use the replace feature. In "Find what" I type Apple and in "Replace with" I type Orange and then press Find all, I get the message saying 6 cell(s) found. Now, I press Replace all and the mes...

Large Line-Spacing on Replies
When I reply to certain other Outlook user's messages, the spacing between each line of my reply is enormous. How can I easily reset it to normal line spacing? Thanks, Bryan Easily? That depends on your definition of easily. The only way I know of is to tweak the text formatting (line spacing, spacing before/after, etc.) as you would with any other Word document. If there is a one-step process that actually works, I would sure like to hear about it... Tim "Bryan Elwood" <belwood1grass@airmail.net> wrote in message news:e3QYB3esHHA.400@TK2MSFTNGP02.phx.gbl... &...

leading zeros #8
I am creating a column of passwords some of which need to begin with a zero. When I move to a new cell it automatically drops the initial zero and is making my 4 digit password 3 numbers. Is tehre a way to keep it set as a number not text and still elt it hold the inital 0? I am using Excel 2002. Thanks thanks. that worked. "Sloth" wrote: > use a custom number format of 0000 > > "P. Zicari" wrote: > > > I am creating a column of passwords some of which need to begin with a zero. > > When I move to a new cell it automatically drops the ...

Prices set to Zero When Exported
Does any one know why my new store db store prices are all at $0.00 after exporting the store from HQ Administrator? Hi I have this problem before but when i creat worksheet style250:Update inventory-Item. It is fix try make it. "Darcy" <Darcy@discussions.microsoft.com> wrote in message news:83ACE9EA-5D44-47A9-B587-2F2D99E34C83@microsoft.com... > Does any one know why my new store db store prices are all at $0.00 after > exporting the store from HQ Administrator? Darcy, It crept into the program during one of their hotfixes. 1.3R fixes it. -- * Get Secure!...

Looking for some advice -- replacing IE with custom app
<Background> Our company has been selling a product which relies heavily on the use of Internet Explorer (IE). But, with the many security issues of the last few years, IE is becoming more difficult to work with. We are contemplating replacing our dependency on IE with a new custom application that we would write. All our current code is written in MSVC/MFC, so this new app would also. As we are planning on writing a custom app to replace IE, we are also looking at adding new custom user interface features that were almost impossible in the old version of html pages being disp...

Is there a way of not showing zero totals in a pivot table?
I have a table with several thousand records and I have created pivot table within Access 2003 to analyse this data. The amount field is currenlty formatted as currency with 2 decimal places. If two records with an account of 123456 and one record with an amount of 500.00 and the other record also with the same account of 123456 and an amount of -500.00, and so on the pivot table the total is showing as 0.00 (one entry cancelling the other). Does anyone know if there is a way to NOT show these zeros in the totals column as there are quite a few totals with zero values which I do...

Create Virtual com port zero on wince 6
Hi All, I am trying to open a virtual comm port (port 0). But i am not able to succeed. Physical com ports are from #1 to #9. So i need to open port zero as virtual for Bluetooth. It is returning invalid handle with get last error as 12. Please find the code snippet for reference. Is there any configuration needs to be done in BSP for this. Please share your experience. /********************************************************/ // Bluetooth.cpp : Defines the entry point for the console application. // #include "stdafx.h" #include <windows.h> #include...

replacing an asterisk
I have a huge legacy database that has asterisks in front of thousand of items. I am trying to get rid of them so that my sort will wor properly and find that since the * is a wild card I can't do it. any ideas?? (I KNOW there's gotta be a way but can't think of it at th moment.) thanks. ront0276 -- Message posted from http://www.ExcelForum.com oops. from the depths of the brain comes ~* !! sorry. ron -- Message posted from http://www.ExcelForum.com ...

Macro to hide rows with zero in specifc column range
Hello I have a workbook with multiple sheets. I would like to put together a macro that will hide the rows in a given range on all sheets. For example, on Sheet 1 the range of cells I need to evaluate for zero are C9:63. If any of the cells in the range are zero, I want to hide the corresponding row. So if C11 is zero, row 11 is hidden. I would repeat the macro for the same ranges on Sheet 2, 3, 4,... Any help is appreciated. Thanks Sub HideRows() For Each sh In ThisWorkbook.Worksheets For Each c In sh.Range("C9:C63") If c.Value = 0 Then c...

Lead source best practices
Hello, Before getting into my question, I know that there are lots of ways to do the things I'm asking about. I am just curious what some people consider as best practices. Hopefully it will lead to a useful conversation for many of the people on this group. I am curious what best practices people use for lead sources. Take something like 'Partner' for example. Do people generally use just 'Partner' and store the partner name somewhere else, or do you build your lead source list up to include the specific names of the partners you're doing business with? S...

Reduce the size of a sheet with empty space
Hello, I have hundred of Excel workbooks with the same space problem. In the news from 1998, I found this problem with Excel 95/98 -- Original problem (I'm experiencing) >Excel doesn't seem free up space (memory) when a large portion of a sheet >is deleted. Other than selecting the active cells and copying to another >sheet, how can I make Excel give up this space? -- Response >XL5, 95, 97 - Highlight all the unused rows and columns, using the row or >column labels. Select Edit>Delete... and save the workbook. >XL5, 95? - close and re-open the workbook. >To...

Replace or Substitute for COMBIN function
I have lots of lines in this format to convert into COMBIN function (39c3 - 37c3) In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3)) I have so many lines in the above format. All I want to change the c to COMBIN with values intact as I have give the example. Some of the lines are in this format. (45c6 / (6c5 x (39c1 - 37c1))) The above line should become ((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1)))))))) Thanks in advance. One way: I assumed you wanted the cells to become a formula. If not, delete the "=" & from the sTe...

Create an empty space within a report
I have a report that I need to create that will print on a very specific paper. The paper has 2 columns, and in order for the information to fit properly within the white space of the paper, I need to have a break of 1/2 inch every 2 inches. The report needs to go down and then across. I have Access 07, and really need help with this problem. SnowFox wrote: >I have a report that I need to create that will print on a very specific >paper. The paper has 2 columns, and in order for the information to fit >properly within the white space of the paper, I need to have a break of ...

Hard drive space for MSCRM 25 users
Hi: What is a good hd size for the new MSCRM installation w/ SQL on a box? There will be 25 users and the company will really only start w/ 25 Account records. Do We have 2 machines - one for CRM one for SQL (Due to problems we were having with both on the same one)... our CRM has a 36gig and SQL 36gig RAID 5 "Do" <doduong12141214@hotmail.com> wrote in message news:eX385hlyDHA.3208@tk2msftngp13.phx.gbl... > Hi: > > What is a good hd size for the new MSCRM installation w/ SQL on a box? > There will be 25 users and the company will really only start w/ 25 A...

Replace zero values with blank
Hi, I have an excel file with demand data per month. It looks something like below but has got 20 000 rows instead of one. 1 0 3 12 3 4 0 10 12 10 11 0 The calculations I need to do on each row are: frequency, median and minimum value. However the calculation needs to be done without zero values. The zero values needs to be replaced by blanks. I have tried CTRL + H to replace the zero values. But it gives me the problem that the zero in 20, 10 etc. gets replaced by blank too. Does any Excel master know how to change all zeros to blanks in a data range? Looking forward on an answer. /Dan...

line spacing in an imported word document
I imported an MS Word (2002) document into Publisher 2002. The Word formating is lost regarding line spacing. Parapraphs with soft line spacing, i.e. not using enter to move to the next line, retains the line spacing. But whenver a line is spaced using enter, or when a blank line is inserted between paragraphs, the space is twice as large as in the original word document. This happens without changing the format in Publisher. For my publication, these spaces are too large. For example, I have a series of short lines center justified. They need to be in regular paragraph spacing, but bec...

Word 2003 view doesn't show space at document top and bottom
I have Word 2003 and Windows 7. All at once, when I open a new document (Ctrl+n) or sometimes an old existing one, the view, which is in Print Layout, gets rid of the space at the top and the bottom of the page. I have to "work around" and click View<Header and Footer<Close. Then it is ok as long as I have the document open. But when I save and close the document and open it again, the same thing happens - the space is gone from the top and bottom. I don't know the correct terminology to describe this, but I hope you can understand. How does this happen,...

Replace Co with Company
I have a [CompanyName] field that I need to replace Co with Company Most of the time Co is at the end of the CompanyName but sometimes it is in the middle of CompanyName Ajax Construction Co Big Construction Co Inc Thank you Be careful. Be very careful. You could use do something as simple, but slow, as open up the table; click on that column, then do a Find and Replace on "co". However (this is the careful part) Construction could come out looking like Companynstruction!!!! So make very sure to back up the entire database and that table before doin...

Help: How do I remove the space at the end of a number?
Guys, This come up with me recently. How do I remove the space at the end of number? EX: 29.15space I'm thinking of using the "Replace" from the Edit menu. I just can't remember how to do it. Can someone remember how to it? I can't add the numbers because of the space. -- japorms ------------------------------------------------------------------------ japorms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6544 View this thread: http://www.excelforum.com/showthread.php?threadid=557002 Edit > Replace > type a space in the F...