+ Reply to Thread
Results 1 to 9 of 9

macro Delete string text between brackets in several cells

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    3

    Red face macro Delete string text between brackets in several cells

    I am very new to VB, but I need a small macro that performs the following:

    I select a number of cells, and then when I run the macro, it should look for text in brackets and delete it, including the brackets:

    the car (2) provided with a roof (8 and 9) comprising
    - a clutch (34) with a hand brake (3)

    etc

    should result in

    the car provided with a roof comprising
    - a clutch with a hand brake

    I more or less managed to substitute a concrete text (for example "clutch") but here the macro has to look for "(" with

    FIND("(";B10) and afterwards find the next bracket ")"

    and then perform

    REPLACE form "(" to ")" and replace it by ""

    Could someone give me a hand?

    Thanks.

  2. #2
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: macro Delete string text between brackets in several cells

    Try this
    Please Login or Register  to view this content.
    provided your text is in cell A1
    regards

    johnjohns

    When you are not sure where to go, every road takes you there!

  3. #3
    Registered User
    Join Date
    05-26-2011
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: macro Delete string text between brackets in several cells

    It "kind of works".

    If in A1 we have:

    word1 (123) word2 (456)

    it will result in:

    word1 123 word2 456

    no brackets, but still the content of the brackets will be there.

    the result I am looking for is:
    word1 word2

    Thanks a lot

  4. #4
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: macro Delete string text between brackets in several cells

    You can try the find/replace option in excel, where find what is (*) and keep the replace with as empty. And if want to do it with vba, record a macro while replacing.

  5. #5
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro Delete string text between brackets in several cells

    Hi raparigo74
    Maybe a UDF

    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  6. #6
    Registered User
    Join Date
    05-26-2011
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: macro Delete string text between brackets in several cells

    OK, I am very new in VB, but why it does not begin with

    Sub example()

    and ends with End Sub

    I do not manage to make it work

  7. #7
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro Delete string text between brackets in several cells

    hi raparigo74,

    UDF is user defined function.. you can make functions like the built-in excel worksheet functions.

    http://www.techbookreport.com/tutorials/excel_vba1.html

    so place the code in a module you can then use the "Match_Replace" UDF

    eg
    Please Login or Register  to view this content.
    the result in B1 will be "word1 word2 "

    It is recommended to use "Function"s instead of "Sub"s in both worksheets and VBA

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro Delete string text between brackets in several cells

    Hello Pike,

    You could reduce the Pattern to this:
    Please Login or Register  to view this content.

    This will catch all characters rather than space, 0-9,A-Z,a-z, and underscore. If there is a chance that the parentheses could be empty then use this pattern:
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  9. #9
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro Delete string text between brackets in several cells

    Hi Leith,
    thanks for the pointer

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1