[Webinar] Streamline your web hosting managementRegister Today


Finding a text string and removing characters from the string

Posted on 2004-04-10
Medium Priority
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
Question by:ronaldslost

Expert Comment

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.

Author Comment

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


Expert Comment

ID: 10796680
hi does anyone here know any file sharing programs other than kazaa
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 12

Expert Comment

ID: 10796731
  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.
LVL 16

Accepted Solution

warturtle earned 172 total points
ID: 10797322

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:


Hope that helps,



Assisted Solution

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

Replace with ""   ie nothing


Assisted Solution

Phoenixric earned 164 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. .

- 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![Meeting_Place] = cMyString
       End If
    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.

Expert Comment

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Simple Linear Regression
Starting up a Project

608 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