To display number in text format in cells

I have a sequence of digits like
5026555306553

I entered in a cell and when the cursor left the cell it displays as
5.02656E+12

How do I set the cell or the whole spreadsheet to display as text rather
than this format?


0
6/30/2008 10:50:00 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
528 Views

Similar Articles

[PageSpeed] 1

Change the cell format to either:
Number
Accounting
or
Currency.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message 
news:e0UPKOw2IHA.4164@TK2MSFTNGP03.phx.gbl...
>I have a sequence of digits like
> 5026555306553
>
> I entered in a cell and when the cursor left the cell it displays as
> 5.02656E+12
>
> How do I set the cell or the whole spreadsheet to display as text rather
> than this format?
>
> 


0
ragdyer1 (4060)
6/30/2008 11:22:51 PM
*OR*

Format to TEXT *before* entering (pasting) the number.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" <ragdyer@cutoutmsn.com> wrote in message 
news:%23I%2346iw2IHA.3544@TK2MSFTNGP05.phx.gbl...
> Change the cell format to either:
> Number
> Accounting
> or
> Currency.
> -- 
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message 
> news:e0UPKOw2IHA.4164@TK2MSFTNGP03.phx.gbl...
>>I have a sequence of digits like
>> 5026555306553
>>
>> I entered in a cell and when the cursor left the cell it displays as
>> 5.02656E+12
>>
>> How do I set the cell or the whole spreadsheet to display as text rather
>> than this format?
>>
>>
>
> 


0
ragdyer1 (4060)
6/30/2008 11:28:25 PM
Hi,

Careful here, if you paste a number from a range onto a cell that is 
formatted as text the number will be stored as a number not as text, unless 
you tell it to paste values or paste formulas or basically you tell it to 
paste without formatting.

-- 
Cheers,
Shane Devenshire


"RagDyer" wrote:

> *OR*
> 
> Format to TEXT *before* entering (pasting) the number.
> -- 
> HTH,
> 
> RD
> 
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message 
> news:%23I%2346iw2IHA.3544@TK2MSFTNGP05.phx.gbl...
> > Change the cell format to either:
> > Number
> > Accounting
> > or
> > Currency.
> > -- 
> > HTH,
> >
> > RD
> >
> > ---------------------------------------------------------------------------
> > Please keep all correspondence within the NewsGroup, so all may benefit !
> > ---------------------------------------------------------------------------
> > "Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message 
> > news:e0UPKOw2IHA.4164@TK2MSFTNGP03.phx.gbl...
> >>I have a sequence of digits like
> >> 5026555306553
> >>
> >> I entered in a cell and when the cursor left the cell it displays as
> >> 5.02656E+12
> >>
> >> How do I set the cell or the whole spreadsheet to display as text rather
> >> than this format?
> >>
> >>
> >
> > 
> 
> 
> 
0
7/1/2008 12:52:00 AM
I'm assuming, perhaps incorrectly, that the OP is *primarily* concerned with 
displaying *all* the digits.

Off the top of my head, I can't immediately see a difference between the 2, 
except for default left or right justification.
Or ... maybe not totaling with the Sum() function.

Can you think of some others?
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:96C81D1F-B195-4453-959D-86A739D0D234@microsoft.com...
Hi,

Careful here, if you paste a number from a range onto a cell that is
formatted as text the number will be stored as a number not as text, unless
you tell it to paste values or paste formulas or basically you tell it to
paste without formatting.

-- 
Cheers,
Shane Devenshire


"RagDyer" wrote:

> *OR*
>
> Format to TEXT *before* entering (pasting) the number.
> -- 
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message
> news:%23I%2346iw2IHA.3544@TK2MSFTNGP05.phx.gbl...
> > Change the cell format to either:
> > Number
> > Accounting
> > or
> > Currency.
> > -- 
> > HTH,
> >
> > RD
> >
> > ---------------------------------------------------------------------------
> > Please keep all correspondence within the NewsGroup, so all may benefit 
> > !
> > ---------------------------------------------------------------------------
> > "Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message
> > news:e0UPKOw2IHA.4164@TK2MSFTNGP03.phx.gbl...
> >>I have a sequence of digits like
> >> 5026555306553
> >>
> >> I entered in a cell and when the cursor left the cell it displays as
> >> 5.02656E+12
> >>
> >> How do I set the cell or the whole spreadsheet to display as text 
> >> rather
> >> than this format?
> >>
> >>
> >
> >
>
>
> 


0
ragdyer1 (4060)
7/1/2008 2:02:28 AM
Just reading him literally:
"to display as text"


-- 
Thanks,
Shane Devenshire


"RagDyeR" wrote:

> I'm assuming, perhaps incorrectly, that the OP is *primarily* concerned with 
> displaying *all* the digits.
> 
> Off the top of my head, I can't immediately see a difference between the 2, 
> except for default left or right justification.
> Or ... maybe not totaling with the Sum() function.
> 
> Can you think of some others?
> -- 
> 
> Regards,
> 
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
> 
> "ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
> message news:96C81D1F-B195-4453-959D-86A739D0D234@microsoft.com...
> Hi,
> 
> Careful here, if you paste a number from a range onto a cell that is
> formatted as text the number will be stored as a number not as text, unless
> you tell it to paste values or paste formulas or basically you tell it to
> paste without formatting.
> 
> -- 
> Cheers,
> Shane Devenshire
> 
> 
> "RagDyer" wrote:
> 
> > *OR*
> >
> > Format to TEXT *before* entering (pasting) the number.
> > -- 
> > HTH,
> >
> > RD
> >
> > ---------------------------------------------------------------------------
> > Please keep all correspondence within the NewsGroup, so all may benefit !
> > ---------------------------------------------------------------------------
> > "RagDyer" <ragdyer@cutoutmsn.com> wrote in message
> > news:%23I%2346iw2IHA.3544@TK2MSFTNGP05.phx.gbl...
> > > Change the cell format to either:
> > > Number
> > > Accounting
> > > or
> > > Currency.
> > > -- 
> > > HTH,
> > >
> > > RD
> > >
> > > ---------------------------------------------------------------------------
> > > Please keep all correspondence within the NewsGroup, so all may benefit 
> > > !
> > > ---------------------------------------------------------------------------
> > > "Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message
> > > news:e0UPKOw2IHA.4164@TK2MSFTNGP03.phx.gbl...
> > >>I have a sequence of digits like
> > >> 5026555306553
> > >>
> > >> I entered in a cell and when the cursor left the cell it displays as
> > >> 5.02656E+12
> > >>
> > >> How do I set the cell or the whole spreadsheet to display as text 
> > >> rather
> > >> than this format?
> > >>
> > >>
> > >
> > >
> >
> >
> > 
> 
> 
> 
0
7/1/2008 4:34:00 AM
>> I'm assuming, perhaps incorrectly, that the OP is *primarily* concerned 
>> with
>> displaying *all* the digits.
>>
>> Off the top of my head, I can't immediately see a difference between the 
>> 2,
>> except for default left or right justification.
>> Or ... maybe not totaling with the Sum() function.

Yes, I just want to display the whole string of numbers as string or text 
but not the exponential format.

The reason is the cell will contain a barcode or serial number that is all 
numerics. 


0
7/1/2008 5:01:42 AM
The General format will default to scientific notation when the digits 
exceed 11.

Thanks for the feed-back.
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Man T" <alanpltse_NOSPAM@yahoo.com.au> wrote in message 
news:OabU3dz2IHA.1808@TK2MSFTNGP04.phx.gbl...
>> I'm assuming, perhaps incorrectly, that the OP is *primarily* concerned
>> with
>> displaying *all* the digits.
>>
>> Off the top of my head, I can't immediately see a difference between the
>> 2,
>> except for default left or right justification.
>> Or ... maybe not totaling with the Sum() function.

Yes, I just want to display the whole string of numbers as string or text
but not the exponential format.

The reason is the cell will contain a barcode or serial number that is all
numerics.



0
ragdyer1 (4060)
7/1/2008 11:35:43 AM
Reply:

Similar Artilces:

Logic Control Pol Display Config
Hi I am trying to LCPD configer pole display. When do all the steps to configer it takes over se 100 pinpad I mean pin pad goes offline. Any Help Check for Port Conflicts Look in Device Manager and see what port each has been assigned. "Ray" <Ray@discussions.microsoft.com> wrote in message news:33E61630-086B-488C-9842-035E073C228D@microsoft.com... > Hi I am trying to LCPD configer pole display. When do all the steps to > configer it takes over se 100 pinpad I mean pin pad goes offline. Any Help On Jan 22, 7:54=A0pm, Ray <R...@discussions.microsoft.com> wrot...

get Cell to not display ( $
hi, i have cells that are formatted for accounting and the cell formula contains the SUM feature, when the cells are empty and waiting for a value, they display ($ -) and so i have those symblos everywhere and would like the cell just to be blank and then show up with the dollar sign when a value appears in it. Any ideas? thanks Try this. There might be an easier one, but this is what I use. S 14 5 15 5 =IF(SUM(S14:S18)=0,"",(SUM(S14:S18))) 16 5 17 5 ...

urgent , display to print
on a landscape print i would like to print text example "excel statistics" on left but vertical. when i print i would like it running from the top to bottom on the left, instead of starting where the data in the sheet starts. I mean when i print this way it leaves a gap at the top and bottom but i would like it without the gap covering like a heading on the left side of paper. Do i need to use something like a clip art or anything else to achieve this ? Any help will be great. thanks I think I'd use another application (MSWord) and print that separately. Then send the ...

Comment print format
Hello, I am trying to change the text that says Cell: Comment: when I print my Comments at the end of my form. Is this text embedded by Microsoft? Thanks, Bob Hi Bob have a look at this thread http://tinyurl.com/3xq6h Frank GT40 wrote: > Hello, > I am trying to change the text that says Cell: Comment: > when I print my Comments at the end of my form. Is this > text embedded by Microsoft? > Thanks, > Bob Frank OP wants the printed comments to not show the Cell:A1 Comment:whatever The codes at your URL just change the on-sheet comment format, but the printed ...

Queries about changing date format
I have a table that has a date in it and it also has the time. I need to get rid of the time and leave the date. What is the best way to do this? -- Thomas In a query? Just use format to return the date you want. DateOnly: Format([YourFieldName],"short date") -- Hope that helps! RBear3 .. "T Miller" <TMiller@discussions.microsoft.com> wrote in message news:7B0A8BD4-4E3B-44B5-9A8D-3522F150E469@microsoft.com... >I have a table that has a date in it and it also has the time. I need to >get > rid of the time and leave the date. What is the best w...

Sepatate positive and negative numbers
Hello Is it possible to automatically separate positive and negative numbers (precedeed by a minus sign) from one column into two different columns ? I'm using Excel 97. Thank you Joe If you have your list of numbers in Col A starting in Row 1 Put this formula in B1 =if(A1>0,A1,"") for pos #'s Put this formula in C1 =if(A1<0,A1,"") for neg #'s Copy the two formulae down as far as needed Note that zero will not show in either col B or -- Cutte ----------------------------------------------------------------------- Cutter's Profi...

formula to replace cell with over 8 characters with word LAN
Hello Group, I hope someone will be able to help. I need a formula that can check each cell in column "D" and if it has over 8 characters in the cell, return a value of the word LAN in column "F". Anything with 8 or under should remain as is. There will be up to 600 row entries in column "D". example of col: 799Z 777X 79BM 08/30/06(An) 08/30/06(Put) 08/30/06(InT) 76CC I'm aware that with all the talent and expertise in the group, this is small potato's, but it is greatly appreciated. Thank you, Calop ?... =IF(LE...

how can i get a cell to hold a zero in front of a digit ie. 01 or.
I have tried checking the zero values box but still just takes the zero from the front which I need for the table I am trying to plan. I don't want any decimal points in front You can do it three ways 1) If the 'number' you want to end up with is fixed length, say six characters, then Format>Cells...>Custom>000000 will do it 2) Format the cells as text in advance of entry 3) Prefix with a single apostrophe ('). It will not show but will treat the entry as text -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS ...

How do I get macro to unmerge cells that have been previously merg
When I try to record the steps to unmerge 2 previously merged colums into 2 separate colums into a macro, when the macro is executed it tries to merge the colums instead of unmerging them? If I do the same steps without recording as a macro the unmerge works fine. Is there a trick invloved in recording a macro to do unmerging? Hmmmm not sure what's happening. Are you sure the cells you ar unmerging are merged (probably a daft question), merge/unmerge is toggle. Keyboard strokes are better than the mouse when using the record macr function, eg Ctrl+1 Alt+m Ente -- tghcog -------...

Getting a number to update
I have a form with a button that when clicked I would like to have it go to a file, get the number there, add one to it, and then save it back in that file as well as display it in the field on the form. I created a table for this number, but don't know how to program the button to do what I want it to do. The users need a new number only some of the time, and would then click the button. The number needs to be incremental by one. Would a query work best or an update? I am at a loss, please help. Thanks, First BackUp your data I assume that this table has only one record whi...

How do I count the number of "yes" boxes?
I am helping plan a conference and would like to use Access to manage/track the registrations and mailings. I have managed to get the names and addresses for the people we are inviting entered into a table. I have also added to the table a number of columns with yes/no options (examples: are they coming, are they bringing a spouse, have they paid yet). I would like to know how to get queries that will count the yes or no answers (examples: how many people are coming, how many people are having Wednesday dinner). I don't know much about Access so any suggestions will need to be r...

Cell Macro
I'm creating a stationery order form as a spreadsheet which uses the "IF" function to decide which items go on the final form. I need a way to hide the cells which aren't being used or move all the used cells to the top of the form, because at the moment Excel is printing out 4 pages of boxes when only one item is being ordered. Can anyone help? Thanks, Eilidh Eilidh This should do it: Sub HideRows() Dim rng As Range Set rng = Range("B1:B400") 'alter this range to suit the Qty range rng.Select 'Make sure all row are visible Selection.EntireRow.H...

must press enter 2x to move cell focus
I now have to hit enter or tab twice after editing cell data to get cursor to move to next cell, but only on existing workbooks. New workbooks stil moves after first enter. Move after entry box is checked, tried unchecking, reopen excel, recheck, reopen excel, not fixing it. Excel 2003 on Win XP Pro, AV files current and running. Don't think I changed anything lately. Have compared settings, can't find any differences. Searched KB and newgroup posts but if answer is there I'm not searching on correct words... ...

Email from CRM showing sender's email address, not display name
We recently installed CRM 4.0 as an upgrade from 3.0. When an email activity is generated and sent, the resulting email in Outlook only shows the senders email address in the From field, not the Display Name. As a matter of fact, the display name is not even included in the message header. We are using the CRM Email Router to send using SMTP. This happens whether the email is from a User or Queue. Anyone seen this yet? Is this a CRM issue or an Exhange issue. I seen it and then it disappeared :) publish the email router again . are you generating email through workflow? send emai...

round up the number
Hi, I have a table below: A B C 1. 123.60 12 2. 178.40 8 How can I set a formular to get the following results: C1 = 124 * B1 (A1's 123.60 round up to 124) C2 = 178 * B2 (A2's 178.40 round up to 178) thanks in advance! Lowan -- Lowan Try: =ROUNDUP(A2,0)*B2 "Lowan Chan" wrote: > Hi, > > I have a table below: > A B C > 1. 123.60 12 > 2. 178.40 8 > > How can I set a formular to get the following results: > C1...

How to create a pie chart with 793 numbers? Possible?
Hi, I've been trying to create a pie chart with 793 numbers on one single colum, the column consists of various numbers and i would like to take a percentage of numbers on the column, for example there are 100 tens ranging from cell A1 to A99 and 500 1's ranging from cell A100 to A699 and other numbers, is it possilbe to create a pie chart with that many numbers? -- Every time i tried to create the pie chart using the chart wizard, the finished chart will always shown with so many different slices, any tips on how to create the pie chart would be greatly appreciated thanks in adv...

Need to insert x number of rows in a list of data
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Greetings, <br><br>I am trying to insert approx 10 blank rows below each line of data in a spreadsheet. (The extra rows will be used for calculations for each original row.) <br><br>I hope my explanation is sufficient; please let me know if not. <br><br>I suspect my dilemma can be fixed easily, but I have searched online for a number of hours to no avail. Under a previous version of Excel, I could have created a macro to semi-automate this, but have no idea about Excel 2008 ...

Can't print bank account number on checks
I'm using Money 2005 and I can't get any of my information to print on my checks (my bank name, address, checking account number, etc), but I can get all of the payee information to print...Can anyone help me? Email responses here & mplsgal@mn.rr.com Thanks! In microsoft.public.money, Lee wrote: >I'm using Money 2005 and I can't get any of my >information to print on my checks (my bank name, address, >checking account number, etc), but I can get all of the >payee information to print...Can anyone help me? See FAQ available at http://umpmfaq.info/faq.h...

Ignoring Blank/Empty Cells that contain formulas
I have a column row of formula's that take data from another worksheet, when there is an error in the original data it leaves the cell blank. Formula =IF(ISERR('JAN 2005'!Y55),"",'JAN 2005'!Y55) My problem is that there is a graph generated from this column and it treats all the blank cells as zero, this messes up the graph and shows it dropping to zero. (The errors alway happen at the end of the data) Is there a way to get the graph to ignore the zero figures or rebuild the formula to show the cell as truly blank? Regards Paul Change the formula to ...

Text to column, fixed width
I have a chart based on imported data from a database. The problem is after anytime I refresh data, the chart is colasped, all data become straight line at 0. I have to click data/text to column/fixed width/finish to get my chart back. My question: How can I format the worksheet so that I don't have to do this after refresh data? Thanks Daniel You could record a macro to do the import followed by the parsing (text-to-columns) best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Daniel" <Daniel@discussions.microsoft.com> wrot...

Getting Visual Studio Output Window Text
Hi, When the program loads up, I would like to display the DLL's being loaded like the way it does in Visual Studio Output window, or like the way Adobe reader initializes. Any leads? Thanks, -- Christopher J. Holland [!MVP] http://www.mvps.org/vcfaq/ http://www.codeguru.com http://www.codeproject.com http://www.naughter.com/ http://support.microsoft.com/default.aspx http://msdn.microsoft.com/howto/ http://msdn.microsoft.com/library/ www.flounder.com/mvp_tips.htm ...

Outlook 2003
Hi Am running outlook 2003 on a new Core2 duo running Vista Home Premium with all M$oft updates applied. When I receive emails that are supposed to have photos attached, I often receive them in plain text format with HTML code. The pictures are converted to a bunch of spurious characters eg; --_ab33f5dc-d792-409c-946f-7e6261a82e82_-- --_0b4657a7-168c-456f-a2b3-35bc796bdbf1_ Content-Type: image/jpeg Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="IMG_0434.JPG" /9j/4AAQSkZJRgABAAEAtAC0AAD//gAfTEVBRCBUZWNobm9sb2dpZXMgSW5jLiBWMS4wMQD/2wCE AAwICQsJCA...

Convert Excel to comma delimited text #3
Thank you! That did the trick. -- deacs ------------------------------------------------------------------------ deacs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2321 View this thread: http://www.excelforum.com/showthread.php?threadid=266960 ...

Stop displaying a line
I have a line graph/chart and it gets its values using a formula and the values of another column. However this poses a problem because if there is nothing in the targetted column the formula returns 0 cause the line to drop to the bottom, instead of casuing the line to just end until a value is added to the target column. How can I do this? Change your formula to use NA() if the cell is empty. For example: =IF(A1="",NA(),A1) To hide the resulting #N/A errors on the worksheet, you can use conditional formatting. There are instructions here: http://www.contextures.com/xlC...

Increasing the number of files as recent files
Hello When I open file menu, I get the last 4 files I have previously openned. Is there a way to increase the number of recent files I have openned before? Thanks in advance. Hamed... Hi Hamed, You can go up to 9 entries in Excel 2000. Tools, Options, General, [x] recently used files list: -- 9 -- entries. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Hamed" <hamedarab@hotmail.com> wrote ... > When I op...