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