+ Reply to Thread
Results 1 to 4 of 4

Search string for defined set of characters

  1. #1
    Registered User
    Join Date
    11-21-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Search string for defined set of characters

    Here’s my problem:

    I have about 700 cells I want to interrogate.

    Within each cell the following text appears “Estimate – BOLB/02/1234 – Some more text here”

    I want to search all the cells and delete the cell contents but leave the “BOLB/02/1234”.

    The problem I have is that the string is different in each cell but the format is the same. It always starts with BOLB followed by /, followed by 2 numbers, followed by / followed by 4 numbers i.e. BOLB/**/****.

    I am struggling to find a solution to this

    Thanks for any help in advance!
    Last edited by taylormade58; 11-21-2009 at 05:59 PM.

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Search string for defined set of characters

    Hi,

    I hope I am wrong ... but a copy paste would do the job ...???
    Oops ... numbers are different ...

    =MID(A1,SEARCH("BOLB",A1),11)

    HTH
    Last edited by JeanRage; 11-21-2009 at 04:30 AM.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Search string for defined set of characters

    EDIT: below was in response to above pre-edit, now post-edit it is now in essence a repetition of the above.

    taylormade58, are the 700 cells in a contiguous range and does the string of interest appear in each ?

    If for ex. the values were in A1:A700 you could use temp formulae to do the extraction in B1:B700

    B1: =MID(A1,SEARCH("BOLB/??/????",A1),12)
    copied down

    Then copy B1:B700 results and paste over A1:A700 as values.

  4. #4
    Registered User
    Join Date
    11-21-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Search string for defined set of characters

    JeanRage / DonkeyOte - Wow, Thanks for the help. Problem solved.

    Thanks again!

+ 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