Finding Character number for ALT-Enter

[Bob Ptacek]

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.

 

[Sheeloo]

Alt-Enter is Char(10)

 

[Sheeloo]

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))

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what those
characters are:
http://www.cpearson.com/excel/CellView.aspx

 

[Bob Ptacek]

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]

Dave, thank you for the link

Chip Pearson has a very nice addin that will help determine what those
characters are:
http://www.cpearson.com/excel/CellView.aspx

 

[Dave Peterson]

=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]

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]

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.