Solved

Finding a text string and removing characters from the string

Posted on 2004-04-10
10
292 Views
Last Modified: 2010-04-17
I'm sorry to bother you guys with some thing this feeble but here goes:

I am having to import a word table into excel, then I want to strip out only the information i want. some of the tables are different lengths and rows etc.

So I search for the text  ' meeting dates" which returns for example

"MEETING DATES:  9th - 15th January 2003"

I then copy this string to another sheet. Next,  I then want to trim it to 15th January 2003 etc.

i have tried Left, Right etc but none of them seem to work, is it because I am trying to do this function on a selected coloumn instead of cell by cell? I really havent a clue.

Another thing to note is that the date in the tables can change. When  I import the word document i generally get around 80 tables imported into Excel.  

I dont think there is any point at looking at my code because it si sooooo bad, I need soem fresh code with some help, really it would be most appreciated

Ronald, yep I really am lost
0
Comment
Question by:ronaldslost
10 Comments
 

Expert Comment

by:ZafferMohamed
ID: 10796520
I think it would be easier to answer your question if you could give more details as to what exactly you want a solution for.
Please give more details like more samples of the string from where you'd like to extract date from. Also whether you want code for just extracting date from the string or more than that.
0
 

Author Comment

by:ronaldslost
ID: 10796554
First, thank you so much for replying:

You are right there are maybe 3 things I would like to do:

Item 1

In Excel after importing a document with 80 odd word tables I would find a text string "MEETING DATES:  9th - 15th January 2003"

Note the dates can vary but the "MEETING DATES:" is always the same. I would like some code that deletes from the left 22 characters and spaces, so that I am left with 15th January 2003, or whatever the date is in the worksheet.

Item 2

It would be great if it could loop through the remaining tables

Item 3

The sample code some kind person could supply would be used to change the criteria for other searches and string deletions not necessarily just from the left, but from the right and midddle.

I know this is asking for a lot but......

:-)
0
 

Expert Comment

by:Islam370
ID: 10796680
hi does anyone here know any file sharing programs other than kazaa
0
 
LVL 12

Expert Comment

by:farsight
ID: 10796731
Islam370,
  I see that you're new.
  The way this site works, is that you find the right topic area, then you create a new question in that area by clicking the "Ask a Question" link, which is generally over on the left side.

For the topic areas, see http://www.experts-exchange.com/allTopics.jsp   or click the "All Topics" link.

Please, don't post your questions on other people's questions -- unless it's a natural part of the conversation for that question.

Please post your question as a brand new question.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Accepted Solution

by:
warturtle earned 43 total points
ID: 10797322
Hi,

You could essentially search for "-" using the InStr function in VB, and then trim everything after that for obtaining a value like - "15th January 2003".

You could use the WordApplication.tables class to surf through all the tables. You can get some sample VB code for doing that from the website:

http://www.freevbcode.com

Hope that helps,

Regards,

Warturtle
0
 
LVL 1

Assisted Solution

by:MortimerCat
MortimerCat earned 41 total points
ID: 10798654
How about just doing a Find & Replace?

Find  "MEETING PLACE:  "
Replace with ""   ie nothing

0
 
LVL 3

Assisted Solution

by:Phoenixric
Phoenixric earned 41 total points
ID: 10840205
Hi :-)
I understand that you would like to have a dynamic way of concantenating a text depending on the text criteria you specified and wether you want to do the concantenation from left to right and vice versa or even starting in the middle. I suggest you may do this dynamically or even  much easier if you will use database manipulation. All you have to do is to import your imported word --> excel to Microsoft access.You just need to name some important collumns,just like the collumn of the text you want to concatenate. This will create a new tables with fields (which is the name of each collumn you specifed) that you can manipulate using the data control and some of the concantenation function (LTrim for left,RTrim for right and Mid for middle) in Visual basic. Also you can now loop to the rest of the records and manipulate the data you want. But you must have a knowledge in database manipulation in VB to do so. .

e.g.
- let's say you have a collumn in excel that you have imported in M.S Access 97 named Meeting_Place,which contains the text you want to concatenate
- You need a datacontrol and a command button and set(browse) the databasename property of the data control to the database you have created by importing from Excel. Then the recordsource property of the table which contains the field(collumn) of the text.

sample code :
Dim cMyString As String
    Do Until Data1.Recordset.EOF
       If InStr(Data1.Recordset![Meeting_Place], "Meeting") > 0 Then
          ' You should set the starting number of character and
          ' the lenght of the character you want to retain
          cMyString = Mid(Data1.Recordset![Meeting_Place], 22, 23)
          Data1.Recordset.Edit
          Data1.Recordset![Meeting_Place] = cMyString
          Data1.Recordset.Update
       End If
    Data1.Recordset.MoveNext
    Loop
    MsgBox "All Done!"
End Sub

After which, you can import again the data created in MS Access to MS Excel. . Hope this one helps. Try to email me if have more concerns(Phoenixric@Yahoo.com) especially in more detailed coding. Thanks.
0
 
LVL 3

Expert Comment

by:Phoenixric
ID: 10924873
Sorry to post an email. Im new in experts-exchange and not yet aware of all the dos and donts. . :-(
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
strDist challenge 35 97
Programatically extract date from website 8 67
Plain Text Editor for iPad 6 60
"Black Box" Testing of Control System Software 2 25
This article will show, step by step, how to integrate R code into a R Sweave document
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now