# Solved: Find and Copy text Excel



## GaletonDesigns (Apr 11, 2008)

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.


----------



## Rollin_Again (Sep 4, 2003)

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 (Apr 11, 2008)

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


----------



## Rollin_Again (Sep 4, 2003)

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 (Apr 11, 2008)

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.


----------



## Rollin_Again (Sep 4, 2003)

Try this code. Change the number 1 in the first line of the code to reflect your true starting number.


```
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 (Apr 11, 2008)

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.


----------



## Rollin_Again (Sep 4, 2003)

Glad to help out. Let us know if you need any more help with your other macros.

Regards,
Rollin


----------

