Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…

636 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