| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sort column when cells contain both text & number (Sample X
How to sort column when cells contain both text and number, that is
Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Sample X
Hreinn,
Use a helper column with the formula =VALUE(MID(A1,LEN("Sample "),LEN(A1))) copied down to match your data, then sort on that column. HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Sample X
I should have added 1 to the first LEN:
=VALUE(MID(A1,LEN("Sample ")+1,LEN(A1))) to account for the possible lack of a space between the prefix "Sample" and the number, in case you really aren't using "sample " =VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Samp
Hello Bernie !
Thanks for your answears. But I get the Error In Formula message from Excel when I put in your formula. I replace the word Sample in your formula with the word syni (=Sample in Icelandic) and I change the A1 into B4 which is where the first cell is. But both of these changes should not matter. The text in the cells has the void between the word syni (=sample) and the number, so in the cells it is written; syni 1, syni 2 etc. (but not syni1, syni2). Do you have any further suggestions ? :o) -- Hreinn "Bernie Deitrick" wrote: I should have added 1 to the first LEN: =VALUE(MID(A1,LEN("Sample ")+1,LEN(A1))) to account for the possible lack of a space between the prefix "Sample" and the number, in case you really aren't using "sample " =VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Samp
Hreinn,
This should work: =VALUE(MID(B4,LEN("Syni ")+1,LEN(B4))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... Hello Bernie ! Thanks for your answears. But I get the Error In Formula message from Excel when I put in your formula. I replace the word Sample in your formula with the word syni (=Sample in Icelandic) and I change the A1 into B4 which is where the first cell is. But both of these changes should not matter. The text in the cells has the void between the word syni (=sample) and the number, so in the cells it is written; syni 1, syni 2 etc. (but not syni1, syni2). Do you have any further suggestions ? :o) -- Hreinn "Bernie Deitrick" wrote: I should have added 1 to the first LEN: =VALUE(MID(A1,LEN("Sample ")+1,LEN(A1))) to account for the possible lack of a space between the prefix "Sample" and the number, in case you really aren't using "sample " =VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Samp
Hi again Bernie !
Unfortunatelly I still get the same message: "The formula you typed contains an error". I understand the meaning of the function you suggests, but I am not enough familiar with them to see what is missing or is extra to understand why Excel donĀ“t like this formula. Hreinn -- Hreinn "Bernie Deitrick" wrote: Hreinn, This should work: =VALUE(MID(B4,LEN("Syni ")+1,LEN(B4))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... Hello Bernie ! Thanks for your answears. But I get the Error In Formula message from Excel when I put in your formula. I replace the word Sample in your formula with the word syni (=Sample in Icelandic) and I change the A1 into B4 which is where the first cell is. But both of these changes should not matter. The text in the cells has the void between the word syni (=sample) and the number, so in the cells it is written; syni 1, syni 2 etc. (but not syni1, syni2). Do you have any further suggestions ? :o) -- Hreinn "Bernie Deitrick" wrote: I should have added 1 to the first LEN: =VALUE(MID(A1,LEN("Sample ")+1,LEN(A1))) to account for the possible lack of a space between the prefix "Sample" and the number, in case you really aren't using "sample " =VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sort column when cells contain both text & number (Samp
Hreinn,
Send me a copy of your table - clean out any private info that I might sell on the open market -) - and I will have a look. My private address is deitbe at consumer dot org HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... Hi again Bernie ! Unfortunatelly I still get the same message: "The formula you typed contains an error". I understand the meaning of the function you suggests, but I am not enough familiar with them to see what is missing or is extra to understand why Excel don“t like this formula. Hreinn -- Hreinn "Bernie Deitrick" wrote: Hreinn, This should work: =VALUE(MID(B4,LEN("Syni ")+1,LEN(B4))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... Hello Bernie ! Thanks for your answears. But I get the Error In Formula message from Excel when I put in your formula. I replace the word Sample in your formula with the word syni (=Sample in Icelandic) and I change the A1 into B4 which is where the first cell is. But both of these changes should not matter. The text in the cells has the void between the word syni (=sample) and the number, so in the cells it is written; syni 1, syni 2 etc. (but not syni1, syni2). Do you have any further suggestions ? :o) -- Hreinn "Bernie Deitrick" wrote: I should have added 1 to the first LEN: =VALUE(MID(A1,LEN("Sample ")+1,LEN(A1))) to account for the possible lack of a space between the prefix "Sample" and the number, in case you really aren't using "sample " =VALUE(MID(A1,LEN("OtherWord")+1,LEN(A1))) HTH, Bernie MS Excel MVP "Hreinn" wrote in message ... How to sort column when cells contain both text and number, that is Sample 1 Sample 2 Sample 3 etc. When I sort as usual I get the undesired order Sample 1 Sample 10 Sample 100 Sample 101 etc. Instead of Sample 1 Sample 2 Sample 3 etc. This is problem is probably related to that Excel takes the cell content only as text. Thanks in advance. -- Hreinn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
| Completely baffled on what should be simple | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Sort or Filter option? | Excel Worksheet Functions | |||
Linear Mode
