+ Reply to Thread
Results 1 to 8 of 8

extract partial string from Cell

  1. #1
    Registered User
    Join Date
    04-27-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Talking extract partial string from Cell

    Hi like so many I am new to VBA. My problem is to extract a partial string from a cell under certain conditions. I have done it in Excel, but need a VBA solution if possible. The file is 15000 rows long, and changes daily.
    If Column E has he following text. Joblist 0381C - FDS400-203 - H/W SSFDR provided
    Only when I have Joblist, and provided then I need to copy the four digits and letter after joblist into a new cell a few columns to the right. Otherwise ignore the cell. There are always four digits, but there may or may not be a letter at the end. I can ignore everything else.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: extract partial string from Cell

    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

  3. #3
    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: extract partial string from Cell

    Hello sydcoco,

    Welcome to the Forum!

    This macro will search the text of each cell in column "E" starting in row 1 for the words "Joblist" followed by at least one space, 4 digits with or without a alpha character, and the word "provided". Case is ignored in the search and the cell text can be any length.

    Any matches will have the number copied to the adjacent cell in column "G". You can change the starting cell in column "E" to where you want to start and the result column from "G" to the column you choose.
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-27-2013 at 11:20 PM. Reason: Changed Rng.Column to SearchRng.Column
    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!)

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: extract partial string from Cell

    Hi Syd,

    Something like this:

    Please Login or Register  to view this content.
    Change the red number to however far you want the offset to be.

    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Press ALT + F11 to open the Visual Basic Editor.

    Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    Last edited by xladept; 04-28-2013 at 02:11 AM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Registered User
    Join Date
    04-27-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: extract partial string from Cell

    Hi Leith,

    I am speechless. It worked. I fixed up one typing error for the Message box, and corrected a dimension statement. SearchRng instead of Rng.
    I was trying to solve this for days. Thank you so much.

    SOLVED

  6. #6
    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: extract partial string from Cell

    Hello sydcoco,

    Sorry about the typos. Glad you were able to fix them and get it working. I will change the code I posted to reflect what you did. Thanks.

  7. #7
    Registered User
    Join Date
    04-27-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: extract partial string from Cell

    Hi Leith,

    I can't get the message box working for me, to me the message box is a nice to have, but not a must. i am happy.

  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: extract partial string from Cell

    Hello sydcoco,

    The message box will only display if the LastCell row in column "E" is less than the FirstCell row. In this macro the starting cell (FirstCell) is "E1". The lastCell row will never be less than 1.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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