1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Find and Copy text Excel

Discussion in 'Business Applications' started by GaletonDesigns, Apr 13, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. GaletonDesigns

    GaletonDesigns Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    8
    Hello,

    I'm new to spreadsheets and would appreciate any help. I tried using the Macro recorder to do this task but it seems to only work for the cell that I recorded the macro on.

    Here's what I'm trying to achieve.

    I have text in colum A. I would like to find all instances of "BW" in column A and Copy the "BW" to the corresponding column B

    Using the macro recorder only copies it in that one cell.

    Thanks for the help.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I'm a bit confused. Will the string "BW" be the only text in Column A or will "BW" be part of a bigger string in the cell? It sounds like a simple formula would suffice in Column B instead of having to use a macro.

    Can you upload a sample workbook or provide some of the actual values that will be contained in column A?

    Regards,
    Rollin
     
  3. GaletonDesigns

    GaletonDesigns Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    8
    Each line below represent a cell. As you can see, the BW only occurs in certain cells. I would like to be able to cpy the BW to the corresponding cell in the next column (Col B)


    Purple w/White, Orange, & Red Tampo's, w/UH's Hong Kong
    Purple w/White, Orange, & Red Tampo's, w/UH's Malaysia
    Yellow, w/CT's
    Red w/""ZZ-Top"" Tampo's w/BW's
    Yellow w/BW's
    Metallic Blue ""57 Chevy"" on side, GHO's

    I hope this is helpful
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You should be able to get by using the built in FIND or SEARCH function in Excel.

    Assuming that your data starts in Row 1, put the formula below into cell B1 and click Enter. You can then copy the formula down for all other rows by right clicking the cell containing the newly added formula (cell B1) and selecting "COPY." Next select the range of cells in Column B that you want to copy the formula to and right click and choose PASTE SPECIAL and select FORMULAS)

    =IF(ISNUMBER(FIND("BW",A1,1)),"BW","")


    Regards,
    Rollin
     
  5. GaletonDesigns

    GaletonDesigns Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    8
    Thanks, but now I have the problem if there is any information in col B other than BW it wipes it out and leaves a blank. I have different things that I need to fill in column b that has to be copied from column a that's why I thought I needed a macro that I could modify.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try this code. Change the number 1 in the first line of the code to reflect your true starting number.

    Code:
    Sub FindBW()
    
    'Change this to reflect your true starting row.
    vStartRow = 1
    
    For i = vStartRow To Cells(Rows.Count, "A").End(xlUp).Row
    
    If InStr(1, Range("A" & i).Value, "BW") Then
    Range("B" & i).Value = "BW"
    End If
    Next i
    
    End Sub
    If this does not work for you plese post a sample workbook so I can see your data layout and how your data appears in Column B ? Just use the manage attachments button at the bottom of the posting Window.

    Regards,
    Rollin
     
  7. GaletonDesigns

    GaletonDesigns Thread Starter

    Joined:
    Apr 11, 2008
    Messages:
    8
    Your macro works perfectly. Thanks so much for the help. I'm going to study this and see if I can work out my other macros.
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Glad to help out. Let us know if you need any more help with your other macros.

    Regards,
    Rollin
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/703238

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice