converting numbers to text

I have a cell that is formatted as a number, I need it converted to text.  
The issue is that they are 3, 4 or 5 digit numbers in those cells.  I need 
the following conversions to happen:

492 converts to 00492
1132 converts to 01132
80605 converts to 80605

and I need the cell in text format...I've tried using Text to Data, 
reformatting the cell, everything I can think of...can anyone help?

thanks in advance for your help!
0
peterm (65)
9/12/2008 3:04:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
451 Views

Similar Articles

[PageSpeed] 35

Maybe you can just give the column a custom number format:
format|cells|number tab|Custom:
00000

or you can insert another column and then use:
=text(a1,"00000")

drag it down, convert it to values and delete the original.

With the first method, the value will still be a number.  In the second method,
you'll be working with text.



PeterM wrote:
> 
> I have a cell that is formatted as a number, I need it converted to text.
> The issue is that they are 3, 4 or 5 digit numbers in those cells.  I need
> the following conversions to happen:
> 
> 492 converts to 00492
> 1132 converts to 01132
> 80605 converts to 80605
> 
> and I need the cell in text format...I've tried using Text to Data,
> reformatting the cell, everything I can think of...can anyone help?
> 
> thanks in advance for your help!

-- 

Dave Peterson
0
petersod (12004)
9/12/2008 3:15:49 PM
On Fri, 12 Sep 2008 08:04:02 -0700, PeterM
<PeterM@discussions.microsoft.com> wrote:

>I have a cell that is formatted as a number, I need it converted to text.  
>The issue is that they are 3, 4 or 5 digit numbers in those cells.  I need 
>the following conversions to happen:
>
>492 converts to 00492
>1132 converts to 01132
>80605 converts to 80605
>
>and I need the cell in text format...I've tried using Text to Data, 
>reformatting the cell, everything I can think of...can anyone help?
>
>thanks in advance for your help!

If you want it *formatted* in place without changing the data, use
"00000" as the custom format.

If you want to convert it to text in another cell, use
"=text(A1,"00000")".
0
SquarePeg (160)
9/12/2008 4:43:30 PM
Hi Peter,

If you choose the TEXT(A1,"00000") idea, then you may want to select all the 
formula and choose Copy, Edit, Paste Special, Values.  Finally, replace the 
original numbers with the converted text.

Cheers,
Shane Devenshire
Microsoft Excel MVP

"PeterM" <PeterM@discussions.microsoft.com> wrote in message 
news:37DBAD38-FA4E-473D-8C43-30F803C800B4@microsoft.com...
> I have a cell that is formatted as a number, I need it converted to text.
> The issue is that they are 3, 4 or 5 digit numbers in those cells.  I need
> the following conversions to happen:
>
> 492 converts to 00492
> 1132 converts to 01132
> 80605 converts to 80605
>
> and I need the cell in text format...I've tried using Text to Data,
> reformatting the cell, everything I can think of...can anyone help?
>
> thanks in advance for your help! 

0
9/13/2008 6:04:47 AM
Reply:

Similar Artilces:

convert the functions and formulas to values
Hi How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste special as value) Thanks Why? Look at Value in the help for VBA Range("A1").Value = Range("A1").Value -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Mireille Abi Nader" <mireillea@newhorizons.com.lb> wrote in message news:7D82E49E-8709-4930-B8D6-7ED311A1F8C9@microsoft.com... > Hi, > How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste ...

Tracking Number on SOP Invoice Form
I am trying to get the Tracking number that is entered in the Sales User Defined Fields Entry Window onto the SOP Long Invoice Form. Does anyone know what tables to create the relationship with? I know the SOP10107 table holds the tracking number into and I was able to get the table in the table list but i must have the incorrect relationship because nothing appears on the report when i add the tracking number. -- Thanks Knavas Hello Knavas, If you are on GP 7.5 SP2 or higher you can refer to KB article 874762. If you are on GP 7.5 SP1 or lower you can refer to KB 859212. Both art...

how can text be set up to generate a number?
i would like to a word generate a number, i.e.> enter "apples"in one cell and excel generate "352" in a different cell for items like inventory. Basically You will need to set up a 'Lookup' table somewhere with Apples 352 In two columns. Complete this for all entries and then if you are going to enter 'Apples' on another sheet in A1 then in A2 enter =VLOOKUP(A1,Your_Lookup_Range_Address,2,FALSE) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "jwmaes" <jwmaes@discussions.microso...

can u convert a mac font to pc for me? (reply and i'll send it to ur e mail)
I doubt anyone will respond positively to your request. Fonts are usually copyrighted, and converting them would be a violation of that copyright. This is especially true if the font is a commercial font. Can you not find a Mac equivalent? I believe I've run across freeware converters for just this purpose. You might try versiontracker. I know I found something to convert some fonts I absolutely needed in a bind (but I have since forgotten). "serdar" <serdarsoy@yahoo.com> wrote in message news:elqKvj0cFHA.1356@TK2MSFTNGP10.phx.gbl... > > In addition,...

problem with wrap text
I tried looking all through the newsgroup and saw nothing that approaches my query. I do a lot of data editing, working through other people's stuff and trying to standardize it, and that entails a lot of global replaces. It is s.o.p. for me to set |Format|Alignment|Wrap Text| to off, to compact a worksheet and to determine when someone decided to write a book where a single word is needed. Somehow, the Wrap keeps coming back! I have a suspicion that there is some sort of option or default which has got changed, but I know not where or what I have done to deserve this! Any advice...

Converting from upper case to lower case
How do I convert an Excel spreadsheet which is in upper case to lower case (first letter in the cell must be in upper case)? Any help will be greatly appreciated. Thanx Simple =PROPER() -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Simple" <anonymous@discussions.microsoft.com> wrote in message news:29a7c01c464f3$9412d830$a301280a@phx.gbl... > How do I convert an Excel spreadsheet which is in upper > case to lower case (first letter in the cell must be in > upper...

ADO recordset command text vs command
Hi all, I've noticed a slight difference between the way recordset command text works vs commands themselves where SP calls are concerned. We have many calls to SP which also include input and output parameters. When examining a SQL Server trace, for example, the difference is, that we can specify command text to an ADO recordset, which includes input parameter names. However, the ADO command object does not appear to specify any parameter names, although it does support return of output parameters. Is there a way for the ADO command object to also specify the parameter names? This would ...

How do you stop text size increasing in email replies?
I am a new Outlook user and finding it rather frustrating that every time I reply to an email and whenever the recipient responds to me, the text size in each email going back in the email trail gets larger and larger. Does anyone know how to stop this from happening? ...

convert PDF to other vector file format?
Is there any free conversion tool to convert a PDF file into a vector-based file format that can be imported and displayed in publisher? Is there any reason a tool like that has not or cannot be made? I wanted to ask why publisher cannot import PDF format directly but that may be asking too much. The images and text can be extracted from pdf files with another program. Would that do it? -- Don Vancouver, USA "peter" <nospam@nospam.com> wrote in message news:unygw1$9EHA.2016@TK2MSFTNGP15.phx.gbl... > Is there any free conversion tool to convert a PDF file into a v...

Microsoft Excel ASC II Text Files
How do I convert an Excel spreadsheet into this type of file in order to import to another program such as "MaiList & AddressBook" Hi Bill try saving as 'CSV' file in the Save As dialog. This is a comma separated text (ASCII) file HTH Frank Bill Cadwallader wrote: > How do I convert an Excel spreadsheet into this type of > file in order to import to another program such > as "MaiList & AddressBook" ...

Entering Data Into Text Boxes and Worksheet Protection
I'm wondering if there's a way to enter data into a text box once a worksheet has been protected. Currently, once the sheet is protected, there's no way to enter data into a text box. Any thoughts appreciated. Thanks. Stephan There are two different textboxes--one from the drawing toolbar and one from the control toolbox toolbar. But you can rightclick on each and choose: format control (or Format CheckBox There's a tab call protection. Each has a Lock option. (the drawing toolbar has Locked and Lock Text) After I unchecked each of them, I could use them (when I protect...

button text size gets bigger
Hi, I placed a button on my excel sheet to run a macro. Problem is, every time I click on it, the text gets larger. Eventually, I can't even read what it says. Thanks for any help, Matt Are you inserting or resizing cell or rows where the button is. Right click the button, select Format Control, click Properties, click "Don't Move or Size with Cells" Hope this helps! If so, let me know click "YES" below. -- Cheers, Ryan "Matt S" wrote: > Hi, > > I placed a button on my excel sheet to run a macro. Problem is,...

Text-wrapping horizontal axis labels
Hi, I've a small problem which I can't seem to find a solution for - I have created a set of financial reports for a client, which includes a number of charts. The charts are waterfall charts, which I've constructed using a form of stacked bar chart. The labels for the horizontal axis are linked to text alongside the calculations for the charts. The text in the labels is of varying lengths and for some of the charts, this text is being wrapped to two layers on the label that appear on the chart. Every now and then, one of the labels is too long, and instead of ...

Can't see text box fill colour
I have recently reloaded Office Small Business following a hard drive crash. I made some changes to display in Control Panel Accessibility this time that weren't there before. I've changed back to default but the problem continues. Previously I could fill or border text boxes and see the results in both normal view and print preview. Now the fill/border colour is only visible in print preview. Borders appear as black and fills as white in normal view. Text colours are unaffected. This is making life difficult as white text in a black fill just disappears until I go to pr...

Numbers Chart
I need a 10x10 chart of numbers 1-100 with the numbers vertical. I'm sure there is a quickie way to do it. thanks, CB -- C and A Bredt Top left corner of chart: =ROW(A1)+10*(COLUMN(A1)-1) Copy to a 10x10 area. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C and A Bredt" wrote: > I need a 10x10 chart of numbers 1-100 with the numbers vertical. > I'm sure there is a quickie way to do it. > thanks, CB > -- > C and A Bredt > Highlight the cells, format > Cells > Alignment and make it 90 degrees. "Luke...

Text Boxes added to Chart Series
How do I keep text boxes that I've added to a data series with the column they belong on? If I change the size of the chart in anyway, I have to move the text boxes back to their respective spots on the chart. Text boxes are not added to a series, but to the chart. This prevents their sticking to any particular points. Could you add them as data labels? If you use a built-in position (i.e., don't drag them around) they will stick with their associated points - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://P...

Sending large number of emails
Does anyone know of the restrictions and/or procedures using Outlook for sending a large number of emails-same message, large number of addressees, like 1000 or more. Thanks Outlook probably won't be the restriction here but your ISP. Contact them to verify that you are indeed allowed to send that many e-mails as once. When you are allowed to so you can either use the BCC field and put all the addresses in there or use a Mail Merge from within Word. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD...

Division on a totaled number
I have a worksheet where I have a column that has number of participants to attend a class, a column that has the length of class and then a column that has a formula to multiply the number of participants times the length to get a total amount of time trained (example 15 people in a one hour class is a total of 15 hours). The column with the total amount I have auto summed so I get a total number of hours for a month. I want to know take that total for the month and divide it so I know how many minutes that breaks down to. I can not get any formula to come out right for me. I know ...

Change sheet number in formula
Is there a way to do the following. I have a sheet with a formula that reference to another sheet. I need to pull a number from the same cell in every sheet. I want to do a summary of some sheets using the formula... =+'Sheet1'!A1 =+'Sheet2'!A1 Instead of hard coding the sheet number in the above formula, is there a way to reference a sheet number from another cell.?? ie Cell A1 = Sheet1" Cell A2 = "Sheet2" Then have another cell that pulls from A1 =+'CellA1'!A1 Very difficult to explain.... hope that made some sense... Thanks ** Post...

Line separating footnotes from text
How do I correct the left indent on the line that separates my footnotes from the main text? When I brought all my text (body and footnotes) in by a couple of cms, this line remained where it was. Now it's jutting out from the rest of the doc. Thanks Change to Draft View and then on the Reference tab of the ribbon, click on the Show Notes button in the Footnotes section of the ribbon and in the top border of the Footnotes pane that appears at the bottom of the screen, select Footnote Separator from the drop down list and modify the left indent of the paragraph in which th...

Help! Converting to 2007 from 2000
Hello all, We have just installed Access 2007 and are trying to convert the 2000 database to 2007. I converted the database (both FE and BE), enabled the content and added the trusted locations. I still cannot open anything. I even tried opening it as a 2000 format but no luck. When I go to the objects window, the queries are listed but none of the forms, reports or modules appear and I get an error message saying that the MainMenu cannot be found or is mispelled. That's the form that is supposed to be displayed when the database is opened. I spend hours searching the MS websit...

Excel: When printing some cells will not print text in them
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box. Any suggestions on how to get it to include the text in these two cells? ...

Pass a command text in pivot table
I've got a pivot table with data give back by odbc (in excel 2003). When i pass a new sql command, it answer me the database, also if i pass the connection string. My code is this: With ActiveSheet.PivotTables(sNamePivot).PivotCache .CommandText = sSelect .Connection = "ODBC;DSN=DS;DB=" & NomeDB & ";SRVR=;UID=SYSADM;PWD=" .Refresh End With sNamePivot --> string variable contain the correct name of the pivot table. NomeDB --> string variable contain the database. sSelect --> variable contain the sql command. Why does it called me ...

Need to convert Supercalc 5 into Excel
Does anyone know how to do this? Thanks -- rmccoy0025 ------------------------------------------------------------------------ rmccoy0025's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30360 View this thread: http://www.excelforum.com/showthread.php?threadid=500149 You will need a copy of Supercal to save the file in XL format Tried e-bay? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmccoy0025" <rmccoy0025.21h60z_1136990703.8583@excelforum-nospam.com> wrote in message news:rmccoy0025.21h60z_1136990703.8583@ex...

Text box question #2
I have a userform which I have linked to a spreadsheet. I need to know how to get the userform to clear textbox.1 once I hit the enter key I have it working but it clear this textbox. Also how do I get the userform to automatically refresh to show the updated titles in the other textboxes on this form. Thanks in advance Greg ...