Finding Character number for ALT-Enter

B

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.
 
S

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

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.
 
D

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.
 
B

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top