Extracting data from a line in field with written text

[Ainudeen]

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. "Emergingmarket s, 02 Nov 2009"


Can anyone help me in this???

[Guido Geurs]

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(Text Cell 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,

[Guido Geurs]

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 "Descriptio n"


Public Function TextLine(TextCe ll 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,

[Ainudeen]

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