Extracting data from a line in field with written text

I have an article in one cell of an Excel, from which I wanted the words in the second line.


The article is

Exclusive: Goldman hires Mike Workman from UBS ahead of sovereign Eurobond decision
Emergingmarkets, 02 Nov 2009

http://emergingmarkets.me/2009/11/goldman-hires-mike-workman-from-ubs-ahead-of-sovereign-eurobond-decision/

Goldman Sachs have hired Michael Workman from UBS for a senior role in fixed income just as the US investment bank expects to land a key role on Russia’s $17bn Sovereign Eurobond issue.

The Wall Street Bank declined to comment but a source close to Goldman Sachs in Moscow confirmed Workman had already joined their Moscow operation in “a senior role.”


I wanted to take out only the data in the second line i.e. "Emergingmarkets, 02 Nov 2009"


Can anyone help me in this???

re: Extracting data from a line in field with written text

dear,

You can write a function that cuts the 2e line in a text lik this=
- delete the first line by looking for the linefeed.
- capture the second line (first line in rest of string) by looking for the next linefeed.
==========================================

Option Explicit

Public Function SecondLine(TextCell As Range) As String
Dim rest_text As String
'§ cut first line
rest_text = Mid(TextCell, InStr(TextCell, Chr(10)) + 1)
'§ capture second line = first line in rest string
SecondLine = Left(rest_text, InStr(rest_text, Chr(10)) - 1)
End Function

==========================================

See also attachment.

br,

Attached Files

Book1.zip (7.0 KB, 1 views)

re: Extracting data from a line in field with written text

dear,

I have refined the function.
Now you can select any line in a text.

========================================
Option Explicit

'§ !!!! To add a HELPtext to a function:
'§ Open the "Object Browser" (F2) in VBA
'§ Search the function.
'§ RMB - "Properties".
'§ Enter text in "Description"


Public Function TextLine(TextCell As Range, LineNumber As Integer) As String
Dim REST_TEXT As String
Dim i As Integer
'§ cut lines
REST_TEXT = TextCell
For i = 1 To LineNumber - 1
If InStr(REST_TEXT, Chr(10)) Then '§ if there is a linefeed
REST_TEXT = Mid(REST_TEXT, InStr(REST_TEXT, Chr(10)) + 1)
Else '§ LineNumber to big
REST_TEXT = "No line"
End If
Next
'§ capture line = first line in rest string
If InStr(REST_TEXT, Chr(10)) Then '§ if not last line
TextLine = Left(REST_TEXT, InStr(REST_TEXT, Chr(10)) - 1)
Else '§ last line
TextLine = REST_TEXT
End If
End Function

================================================== =======

See also attachment.

br,

Attached Files

Book1.zip (8.2 KB, 7 views)

re: Extracting data from a line in field with written text

Thanks. it will work only when the VBA program is defined...That's working.....