Solved

Finding a text string and removing characters from the string

Posted on 2004-04-10
10
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

707 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