+ Reply to Thread
Results 1 to 8 of 8

Find various strings and copy the found string in column A of the row it appears in.

  1. 03-25-2012, 05:41 PM #1
    equanet
    equanet is offline
    Registered User
    Join Date
    03-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Find various strings and copy the found string in column A of the row it appears in.

    I am constanly working with large spreadsheets with 20,000 rows + and upto columns AB. I have to carry out the same search for various strings like DND, Perm, SP. I need to find these strings within cells and then copy the string found in coulumn A of the same worksheet and highlight the complete row the string is found in. There will be hundreds rows without these strings and hence I would require column A of these rows to be left blank and with no fill colour. Is there a macro capable of doing this? Please help.
    Reply With QuoteRegister To Reply
  2. 03-25-2012, 08:25 PM #2
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Hi

    Have you thought about using a filter and then highlighting the rows that have been returned?

    rylo
    Reply With QuoteRegister To Reply
  3. 03-26-2012, 07:47 AM #3
    equanet
    equanet is offline
    Registered User
    Join Date
    03-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Hi Rylo,

    The text appears in various columns not just one. Also the cells will contain other text not just the text I am looking for. Hence filter would not really work.
    Reply With QuoteRegister To Reply
  4. 03-26-2012, 05:32 PM #4
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Hi

    Can you supply an example file for us to review.

    rylo
    Reply With QuoteRegister To Reply
  5. 03-26-2012, 06:23 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find various strings and copy the found string in column A of the row it appears in.

    You can still use Conditional Formatting. COUNTIF() formulas can use wildcards:

    =COUNTIF(A2:AB2, "*perm*") > 0

    Or:

    =(COUNTIF(A2:AB2, "*perm*") + =COUNTIF(A2:AB2, "*DND*") + =COUNTIF(A2:AB2, "*SP*")) > 0
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  6. 03-27-2012, 12:42 PM #6
    equanet
    equanet is offline
    Registered User
    Join Date
    03-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Here is the example. I have created sheet 1 how I would get the data anhd sheet 2 as what I need excel to do. As you can see the text like DND, PERM, PRM, SP are in different columns and may be mixed in with other text in cells. I need excel to look for this text, when found copy the text found in column A of the same row and highlight the row as yellow. The row does not need to be copied into another worksheet. I have just created to show results. Thanks for your help.

    Book2.xlsx
    Last edited by equanet; 03-27-2012 at 12:57 PM.
    Reply With QuoteRegister To Reply
  7. 03-27-2012, 03:14 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Put this formula in A2 and copy down:

    =SUBSTITUTE(TRIM(IF(ISNUMBER(MATCH("* SP*", $B2:$H2, 0)), "SP ", "") &
    IF(ISNUMBER(MATCH("*PERM*", $B2:$H2, 0)), "PERM ", "") &
    IF(ISNUMBER(MATCH("*DND*", $B2:$H2, 0)), "DND ", "") &
    IF(ISNUMBER(MATCH("*PRM*", $B2:$H2, 0)), "PRM ", "")), " ", ", ")
    Reply With QuoteRegister To Reply
  8. 03-29-2012, 02:55 PM #8
    equanet
    equanet is offline
    Registered User
    Join Date
    03-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Find various strings and copy the found string in column A of the row it appears in.

    Thanks Jerry. This works brilliantly and also because its a formula, much quicker than a macro. Top man. Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1