Sorting numbers as Text

I frequently work with a lists of text data, some of which consists of digits 
or digits + alpha characters. These are always formatted as tect fields.
When I sort them i get a dialog box asking if I would rather sort digits as 
numbers or as text. Sorting as text provides the result I need, so I always 
select that and it works fine
My question is, how can I skip having to answer this question over and over 
and over? Is there a configuration option thats says 'leave me alone - I know 
what I am doing' ?

Thanks for any help.
0
Utf
7/24/2006 11:51:01 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
569 Views

Similar Articles

[PageSpeed] 25

I've never seen the message you refer to - what version of Excel is this?
Sheila

"David M." wrote:

> I frequently work with a lists of text data, some of which consists of digits 
> or digits + alpha characters. These are always formatted as tect fields.
> When I sort them i get a dialog box asking if I would rather sort digits as 
> numbers or as text. Sorting as text provides the result I need, so I always 
> select that and it works fine
> My question is, how can I skip having to answer this question over and over 
> and over? Is there a configuration option thats says 'leave me alone - I know 
> what I am doing' ?
> 
> Thanks for any help.
0
SheilaD (24)
7/25/2006 3:00:02 PM
Not just me then :)

Steve


On Tue, 25 Jul 2006 16:00:02 +0100, Sheila D  =

<SheilaD@discussions.microsoft.com> wrote:

> I've never seen the message you refer to - what version of Excel is th=
is?
> Sheila
>
> "David M." wrote:
>
>> I frequently work with a lists of text data, some of which consists o=
f  =

>> digits
>> or digits + alpha characters. These are always formatted as tect fiel=
ds.
>> When I sort them i get a dialog box asking if I would rather sort  =

>> digits as
>> numbers or as text. Sorting as text provides the result I need, so I =
 =

>> always
>> select that and it works fine
>> My question is, how can I skip having to answer this question over an=
d  =

>> over
>> and over? Is there a configuration option thats says 'leave me alone =
-  =

>> I know
>> what I am doing' ?
>>
>> Thanks for any help.
0
sj_walton (248)
7/25/2006 3:12:29 PM
Sheila D wrote:
> I've never seen the message you refer to - what version of Excel is this?
> Sheila
> 
> "David M." wrote:
> 
>> I frequently work with a lists of text data, some of which consists of digits 
>> or digits + alpha characters. These are always formatted as tect fields.
>> When I sort them i get a dialog box asking if I would rather sort digits as 
>> numbers or as text. Sorting as text provides the result I need, so I always 
>> select that and it works fine
>> My question is, how can I skip having to answer this question over and over 
>> and over? Is there a configuration option thats says 'leave me alone - I know 
>> what I am doing' ?
>>
>> Thanks for any help.

Excel 2003 does this. I run into this when I have product numbers that have 
a leading zero. To keep the leading zero the cells must be formatted as 
text and the prompt comes up whenever you try to sort the column.

gls858
0
gls858 (460)
7/25/2006 3:21:27 PM
I'm using 2003 and I can't get this message even with leading 0's as you 
describe - not aware of any options I've changed either.............

"gls858" wrote:

> Sheila D wrote:
> > I've never seen the message you refer to - what version of Excel is this?
> > Sheila
> > 
> > "David M." wrote:
> > 
> >> I frequently work with a lists of text data, some of which consists of digits 
> >> or digits + alpha characters. These are always formatted as tect fields.
> >> When I sort them i get a dialog box asking if I would rather sort digits as 
> >> numbers or as text. Sorting as text provides the result I need, so I always 
> >> select that and it works fine
> >> My question is, how can I skip having to answer this question over and over 
> >> and over? Is there a configuration option thats says 'leave me alone - I know 
> >> what I am doing' ?
> >>
> >> Thanks for any help.
> 
> Excel 2003 does this. I run into this when I have product numbers that have 
> a leading zero. To keep the leading zero the cells must be formatted as 
> text and the prompt comes up whenever you try to sort the column.
> 
> gls858
> 
0
SheilaD (24)
7/25/2006 3:33:02 PM
Not via the user interface.

But you could record a macro that sorts the data the way you want.  And never be
bother again....

David M. wrote:
> 
> I frequently work with a lists of text data, some of which consists of digits
> or digits + alpha characters. These are always formatted as tect fields.
> When I sort them i get a dialog box asking if I would rather sort digits as
> numbers or as text. Sorting as text provides the result I need, so I always
> select that and it works fine
> My question is, how can I skip having to answer this question over and over
> and over? Is there a configuration option thats says 'leave me alone - I know
> what I am doing' ?
> 
> Thanks for any help.

-- 

Dave Peterson
0
petersod (12005)
7/25/2006 3:58:00 PM
Sheila D wrote:
> I'm using 2003 and I can't get this message even with leading 0's as you 
> describe - not aware of any options I've changed either.............
> 
> "gls858" wrote:
> 
>> Sheila D wrote:
>>> I've never seen the message you refer to - what version of Excel is this?
>>> Sheila
>>>
>>> "David M." wrote:
>>>
>>>> I frequently work with a lists of text data, some of which consists of digits 
>>>> or digits + alpha characters. These are always formatted as tect fields.
>>>> When I sort them i get a dialog box asking if I would rather sort digits as 
>>>> numbers or as text. Sorting as text provides the result I need, so I always 
>>>> select that and it works fine
>>>> My question is, how can I skip having to answer this question over and over 
>>>> and over? Is there a configuration option thats says 'leave me alone - I know 
>>>> what I am doing' ?
>>>>
>>>> Thanks for any help.
>> Excel 2003 does this. I run into this when I have product numbers that have 
>> a leading zero. To keep the leading zero the cells must be formatted as 
>> text and the prompt comes up whenever you try to sort the column.
>>
>> gls858
>>
Here's what I did. I formated cells in the column as text. Entered several
numbers then selected the column went to Data > Sort and a window pops up
that says:
The following sort key may not sort as expected because it contains some 
numbers formatted as text. What would you like to do?

Sort anything that looks like number as a number
Sort number and numbers stored as text separately

Happens every time for me.

gls858
0
gls858 (460)
7/25/2006 4:46:17 PM
Reply:

Similar Artilces:

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Shrinking Text
Hi everybody, in our Office (Word) 2007 application, WTS 2008x64, the following happens (for some - not all- users): A text is written and formatted normally and without any problems. The document shall be printed. After the printjob is executed, the chracters of the text lines are compressed to only a few millimeters in width. The font height remains normal. Nothing is legible. This now ocurs on the printed doc as well as on the screen. After the doc is saved and opened in a different session, everything is OK and works well... What can I do? As always, every help and ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Text Effects Scrolling or Blinking text
How do apply the scroll or blinking feature to text that you want to emphasize in a word document. Prior to MS2007 there was a feature on the fonts tab that allowed a user to apply the following features to text "marching ants, blinking text, scrolling marquee, etc. I cannot find the any of these features anywhere in Word 2007. "HELP" There is no direct access to them, but they are still available by macro I posted the following macro recently, for use in Word 2007 Sub AnimateFont() Dim sAnimation As String If Len(Selection.Range) = 0 Then MsgBox "...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

How do text capture tools work?
Here are two examples of this technology. http://www.textcapture.com/en/default.html http://www.deskperience.com/textcapture/ Sounds like they will have to involve OCR, which works fine for standard fonts but probably won't work with fancy fonts. Key here is to test them try a static control with a TextOut and change the fonts and see what happens. joe On Wed, 31 Oct 2007 15:02:04 -0500, "Peter Olcott" <NoSpam@SeeScreen.com> wrote: >Here are two examples of this technology. > > http://www.textcapture.com/en/default.html > http://www.deskp...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Sort behaves unexpectedly
Over the years I've come to count on being able to sort be a column header. That is, all of the rows below the headers have data and at least two rows above the headers are empty. I have a spreadsheet now where rows 1, 2 and 3 have random info in them. Rows 4 and 5 are empty. Row six has column labels, and rows 7-45 have data. If I put my cursor on one of the headers (e.g., "name"), and click sort a/z, it sorts only the rows in the group, but it sorts the header row into the rest of them. Rows 1-3 aren't affected. How come? I've never had this happen before. ...

Spiral text
Does anyone know how to create spiral text? In a draw program for sure. You probably can get close in Publisher, but it will not look as good as a draw program can do. Serif has a free program you can try. DrawPlus http://www.freeserifsoftware.com/ -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jaykay" <jaykay@discussions.microsoft.com> wrote in message news:71954A65-C46F-4005-9553-B324EFEE7500@microsoft.com... > Does anyone know how to create spiral text? I've downloaded the program, but can't figur...

Email Text Dissappears when Save
When we save email messages (as a draft) the text dissappears, even when th I use my administrator account. Anybody have any idea what that is happening. ...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...