473,327 Members | 2,016 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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???
Nov 4 '09 #1
3 2434
Guido Geurs
767 Expert 512MB
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
File Type: zip Book1.zip (7.0 KB, 80 views)
Nov 6 '09 #2
Guido Geurs
767 Expert 512MB
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
File Type: zip Book1.zip (8.2 KB, 88 views)
Nov 7 '09 #3
Thanks. it will work only when the VBA program is defined...That's working.....
Nov 9 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: lecichy | last post by:
Hello Heres the situation: I got a file with lines like: name:second_name:somenumber:otherinfo etc with different values between colons ( just like passwd file) What I want is to extract...
3
by: dave | last post by:
Hello there, I am at my wit's end ! I have used the following script succesfully to upload an image to my web space. But what I really want to be able to do is to update an existing record in a...
2
by: Aleander | last post by:
Hi! I have to write the records of a vector in a file, e and then open this file to extract the record to refill the vector. My program has two class: Visita(Appointment) and Data(date). The...
5
by: Michael Hill | last post by:
Hi, folks. I am writing a Javascript program that accepts (x, y) data pairs from a text box and then analyzes that data in various ways. This is my first time using text area boxes; in the past,...
4
by: Tony Clarke | last post by:
Hi All, I have been trying to extract data from a text file using the fscanf() functions and sscanf() functions. The file is of various characters and integers separated by semicolons, the...
2
by: Greg Strong | last post by:
Hello All, I've written code in a test database with test data. Everything seems to be working except compact database in VB code per http://www.mvps.org/access/general/gen0041.htm. The reason I...
4
by: Debbiedo | last post by:
My software program outputs an XML Driving Directions file that I need to input into an Access table (although if need be I can import a dbf or xls) so that I can relate one of the fields...
6
by: Amma | last post by:
Hello Every one , Pls help me to extracting number from a text file since I am new to perl programming . I have a file and need to extract the number after semicolon in that ...
1
by: swanc04 | last post by:
Greetings Master of PHP /bow I would like to preform the following task but I'm unable to figure out how, I never really got into Arrays and usually just use work arounds. I would be very greatful...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.