Solved: Find and Copy text Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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.
 
Joined
Sep 4, 2003
Messages
4,916
Glad to help out. Let us know if you need any more help with your other macros.

Regards,
Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top