Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
I have a spreadsheet with columns of data. The data is extracted from a
string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! .... Roger |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Hi Roger
it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Are you sure that you don't have leading characters (e.g., space,
non-breaking space, etc)? Check with LEN(). In article , "Roger" wrote: I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
I have tried using TRIM, but all that happens is that I can see the formula
in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
You need to format your cell as something other than Text before you
enter your formula. In article , "Roger" wrote: I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Hi Roger
If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
This is all very weird !!! I have tried to reformat as you suggest, but it
makes no difference. Even if I type in =TRIM(D2) into a new cell, it still only shows me the formula. It there some global setting for the worksheet which forces the view to formulae ?? that I need to reset or uncheck ? Roger "Roger Govier" wrote in message ... Hi Roger If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Try Roger Govier's suggestion one more time. I bet you did things out of order.
Or maybe you're looking at formulas: tools|options|view tab|make sure Formulas is not checked. Roger wrote: This is all very weird !!! I have tried to reformat as you suggest, but it makes no difference. Even if I type in =TRIM(D2) into a new cell, it still only shows me the formula. It there some global setting for the worksheet which forces the view to formulae ?? that I need to reset or uncheck ? Roger "Roger Govier" wrote in message ... Hi Roger If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Hi Roger
Try Ctrl+` (that's hold down the control key and press the key to the left of 1 on the top row of the keyboard) -- Regards Roger Govier "Roger" wrote in message ... This is all very weird !!! I have tried to reformat as you suggest, but it makes no difference. Even if I type in =TRIM(D2) into a new cell, it still only shows me the formula. It there some global setting for the worksheet which forces the view to formulae ?? that I need to reset or uncheck ? Roger "Roger Govier" wrote in message ... Hi Roger If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Roger
Maybe you are in "Formula View" Hit CTRL + `(backquote above Tab key) Gord Dibben MS Excel MVP On Tue, 29 May 2007 17:50:52 +0200, "Roger" wrote: This is all very weird !!! I have tried to reformat as you suggest, but it makes no difference. Even if I type in =TRIM(D2) into a new cell, it still only shows me the formula. It there some global setting for the worksheet which forces the view to formulae ?? that I need to reset or uncheck ? Roger "Roger Govier" wrote in message ... Hi Roger If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my TEXT function won't work
Yes, I had Formulas checked somehow. Oh for the old days when we had a basic
excel spreadsheet !... however, thanks for all of your help .. Roger "Dave Peterson" wrote in message ... Try Roger Govier's suggestion one more time. I bet you did things out of order. Or maybe you're looking at formulas: tools|options|view tab|make sure Formulas is not checked. Roger wrote: This is all very weird !!! I have tried to reformat as you suggest, but it makes no difference. Even if I type in =TRIM(D2) into a new cell, it still only shows me the formula. It there some global setting for the worksheet which forces the view to formulae ?? that I need to reset or uncheck ? Roger "Roger Govier" wrote in message ... Hi Roger If the formula is showing in the cell, not the result, then it sounds as though the cell was formatted as Text. Format the cell as General, then press F2 to edit, followed by Enter. This should turn it to a formula. Take note of JE's post about the non-breaking space character, as Trim will not remove this. If you find you have a problem after using trim, try =SUBSTITUTE(SUBSTITUTE(D2,CHAR(160),"")," ","") Again copy down, then Paste SpecialValues back over the source column. -- Regards Roger Govier "Roger" wrote in message ... I have tried using TRIM, but all that happens is that I can see the formula in the cell (ie I can see =TRIM(D2)) and it doesn't actually work. Do I need to turn this function on somehow to see the result? Thanks for your help .. Roger "Roger Govier" wrote in message ... Hi Roger it may be that you have some extraneous spaces from your formulae. Assuming your names are in column A, try entering in another column = TRIM(A1) and copy down. Then copy this helper column and Paste SpecialValues back over the original column A Then Sort again. -- Regards Roger Govier "Roger" wrote in message ... I have a spreadsheet with columns of data. The data is extracted from a string using either the MID or LEFT functions, and then I use the TEXT function to make sure it is formatted as text.One column is surnames which I need to sort alphabetically A .. Z. Instead of sorting all of the list alphabetically it sorts most of it, then after Z it has another 20 or so names starting again from A through to Z. Am I doing something wrong ... why won't it sort the whole column of names! ... Roger -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does my Function not work? | Excel Discussion (Misc queries) | |||
how does the AVERAGE function work? | Excel Discussion (Misc queries) | |||
how do i get the mid function to work with a zero | Excel Worksheet Functions | |||
What would cause the sum function in a column not to work? | Excel Discussion (Misc queries) | |||
Why doesn't my =RANDBETWEEN function work? | Excel Discussion (Misc queries) |