Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
I need to count the number of special characters in a cell (ALT-Enter). Based
on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
Alt-Enter is Char(10)
"Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
You can build your own table by filling 1-255 in Col A
and entering in B1 =Char(A1) and copying down If you have a character in A1 you can use in B1 =Code(A1) to get the associated code To find the code for ALT-ENTER, in A1 type A, press ALT-ENTER, type B then enter this in B1 =CODE(MID(A1,2,1)) "Sheeloo" wrote: Alt-Enter is Char(10) "Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
Chip Pearson has a very nice addin that will help determine what those
characters a http://www.cpearson.com/excel/CellView.aspx Bob Ptacek wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
Thank you. That helps find and count the number of "Alt-Enter.".
I don't suppose there is a good way to find their position within the cell. If I have the following values in a cell (where Alt_Enter is -AE-) 1234-AE-567-AE-89 or 123-AE-4567-AE-89. with the count I know there are 3 sets of numbers, but to find the 2nd set they start in position 6 in the first and 5 in the second. The FIND function will tell me the first location but not any subsequent ones. "Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
Dave, thank you for the link "Dave Peterson" wrote: Chip Pearson has a very nice addin that will help determine what those characters a http://www.cpearson.com/excel/CellView.aspx Bob Ptacek wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
=find(CHAR(10),A1,1)
or =find(char(10),a1) will find the first one (starting in position 1) So =FIND(CHAR(10),A1,Find(CHAR(10),A1)+1) will start looking after the first alt-enter character. And find the position of the second alt-enter. You can use this formula, too: =FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),###)) change ### to 1, 2, 3, ... for the first position, 2nd pos, 3rd pos, ... Ron Rosenfeld posted this general formula: =IF(SUBSTITUTE(string,substring,"",n)=string,"not found", FIND(CHAR(1),SUBSTITUTE(string,substring,CHAR(1),n ))) You could use it like: =IF(SUBSTITUTE(A1,char(10),"",2)=string,"not found", FIND(CHAR(1),SUBSTITUTE(a1,char(10),CHAR(1),2))) to find the second alt-enter in A1. Bob Ptacek wrote: Thank you. That helps find and count the number of "Alt-Enter.". I don't suppose there is a good way to find their position within the cell. If I have the following values in a cell (where Alt_Enter is -AE-) 1234-AE-567-AE-89 or 123-AE-4567-AE-89. with the count I know there are 3 sets of numbers, but to find the 2nd set they start in position 6 in the first and 5 in the second. The FIND function will tell me the first location but not any subsequent ones. "Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
Thank you that really helped. I have a string of sets of number seperated by
Char(10) that I need to pull out one at a time and finding hte Alt-E locations will let me use LEn and calcuation to get the sets which are not always same lenght. So this will really help. The fomula may have lots of code, but I can get at the numbers I need. Again thank you "Dave Peterson" wrote: =find(CHAR(10),A1,1) or =find(char(10),a1) will find the first one (starting in position 1) So =FIND(CHAR(10),A1,Find(CHAR(10),A1)+1) will start looking after the first alt-enter character. And find the position of the second alt-enter. You can use this formula, too: =FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),###)) change ### to 1, 2, 3, ... for the first position, 2nd pos, 3rd pos, ... Ron Rosenfeld posted this general formula: =IF(SUBSTITUTE(string,substring,"",n)=string,"not found", FIND(CHAR(1),SUBSTITUTE(string,substring,CHAR(1),n ))) You could use it like: =IF(SUBSTITUTE(A1,char(10),"",2)=string,"not found", FIND(CHAR(1),SUBSTITUTE(a1,char(10),CHAR(1),2))) to find the second alt-enter in A1. Bob Ptacek wrote: Thank you. That helps find and count the number of "Alt-Enter.". I don't suppose there is a good way to find their position within the cell. If I have the following values in a cell (where Alt_Enter is -AE-) 1234-AE-567-AE-89 or 123-AE-4567-AE-89. with the count I know there are 3 sets of numbers, but to find the 2nd set they start in position 6 in the first and 5 in the second. The FIND function will tell me the first location but not any subsequent ones. "Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Character number for ALT-Enter
If you have a column of this kind of data, you could insert a few empty columns
to the right and use Data|text to columns (xl2003 menus) to separate each field. Specify that the data is delimited and type ctrl-j in the Other box. Ctrl-j is another way to enter that alt-enter. Bob Ptacek wrote: Thank you that really helped. I have a string of sets of number seperated by Char(10) that I need to pull out one at a time and finding hte Alt-E locations will let me use LEn and calcuation to get the sets which are not always same lenght. So this will really help. The fomula may have lots of code, but I can get at the numbers I need. Again thank you "Dave Peterson" wrote: =find(CHAR(10),A1,1) or =find(char(10),a1) will find the first one (starting in position 1) So =FIND(CHAR(10),A1,Find(CHAR(10),A1)+1) will start looking after the first alt-enter character. And find the position of the second alt-enter. You can use this formula, too: =FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),###)) change ### to 1, 2, 3, ... for the first position, 2nd pos, 3rd pos, ... Ron Rosenfeld posted this general formula: =IF(SUBSTITUTE(string,substring,"",n)=string,"not found", FIND(CHAR(1),SUBSTITUTE(string,substring,CHAR(1),n ))) You could use it like: =IF(SUBSTITUTE(A1,char(10),"",2)=string,"not found", FIND(CHAR(1),SUBSTITUTE(a1,char(10),CHAR(1),2))) to find the second alt-enter in A1. Bob Ptacek wrote: Thank you. That helps find and count the number of "Alt-Enter.". I don't suppose there is a good way to find their position within the cell. If I have the following values in a cell (where Alt_Enter is -AE-) 1234-AE-567-AE-89 or 123-AE-4567-AE-89. with the count I know there are 3 sets of numbers, but to find the 2nd set they start in position 6 in the first and 5 in the second. The FIND function will tell me the first location but not any subsequent ones. "Bob Ptacek" wrote: I need to count the number of special characters in a cell (ALT-Enter). Based on a similar questions for Tab, the solution for tabs in A9 was =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),"")) I am assuming this would work for me but I can't find how to look up what the values for the 255 character so I can search for the Alt-Enter value. I'm running Office 2003 and would be grateful for any guidance. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change the color of the cell when someone enter any number or character? | New Users to Excel | |||
How can I change the color of the cell when someone enter any number or character? | Charts and Charting in Excel | |||
Finding/counting a given character within a cell | Excel Worksheet Functions | |||
finding rightmost location of a character | Excel Worksheet Functions | |||
Finding a character type within a cell | Excel Worksheet Functions |