Extracting data on third line in a cell

  • Thread starter Thread starter maccboy
  • Start date Start date
M

maccboy

Hi - and thanks for reading this!
I have a csv which I need to run a few format routines on to enable me
to use it. The first column contains data that is wrapped into 4 lines
- using carriage returns (?). I need to extract just the third line of
text. I'm guessing that there is a way of determining where the end of
each line is - but I don't know how!
Thanks!
 
Hi

The carriage returns will be represented by Char(10)
Substituting the third one with a circumflex "^" and then finding that, will
allow the extraction of the 4th line.

=MID(A1,FIND("^",SUBSTITUTE(A1,CHAR(10),"^",3))+1,255)

--
Regards
Roger Govier

maccboy said:
Hi - and thanks for reading this!
I have a csv which I need to run a few format routines on to enable me
to use it. The first column contains data that is wrapped into 4 lines
- using carriage returns (?). I need to extract just the third line of
text. I'm guessing that there is a way of determining where the end of
each line is - but I don't know how!
Thanks!

__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi

The carriage returns will be represented by Char(10)
Substituting the third one with a circumflex "^" and then finding that, will
allow the extraction of the 4th line.

=MID(A1,FIND("^",SUBSTITUTE(A1,CHAR(10),"^",3))+1,255)

--
Regards
Roger Govier











__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

Thanks Roger!

That has sorted that bit of the problem. I'm now working on extracting
the data after the second ^ and before the third ^ (if there is one!)

Cheers.
 
extracting data is always a time consuming process, for extracting
data from documents, the web, websites, or data files, i found
http://www.extractingdata.com to be really useful, they build quick
custom web scraping and data extracting programs
 
Back
Top