Finding a text string and removing characters from the string

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
ronaldslostAsked:
Who is Participating?
 
warturtleCommented:
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
 
ZafferMohamedCommented:
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
 
ronaldslostAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Islam370Commented:
hi does anyone here know any file sharing programs other than kazaa
0
 
farsightCommented:
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
 
MortimerCatCommented:
How about just doing a Find & Replace?

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

0
 
PhoenixricCommented:
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
 
PhoenixricCommented:
Sorry to post an email. Im new in experts-exchange and not yet aware of all the dos and donts. . :-(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.