Find function using chr

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gazza67 - 13 Sep 2007 08:06 GMT

Hi,

I have a single cell with text that contains multiple lines (ie text
separated by a chr(10) I think).

I want to get the text from the cell starting with the first character
up untill but not including the first chr(10).

I presume you use a combination of LEFT , FIND and CHR(10) - but I
just cant seem to get the syntax right.

Could anyone out there help me with this simple problem?

Gary

Reply to this Message

OssieMac - 13 Sep 2007 08:42 GMT

Hi Gary,

Not sure if you want a worksheet formula or a VBA formula because the post
is in a programming forum and examples are worksheet so here is both:-

Worksheet formula assuming cell A1 has the value:-
=LEFT(A1,FIND(CHAR(10),A1)-1)

VBA formula assuming cell A1 has the value:-
Dim strTest as String
strTest = Left(Range("A1"), InStr(1, Range("A1"), Chr(10)) - 1)

Regards,

OssieMac

Reply to this Message