Find/Replace text in Excel 2001

Hello,

try the following formula in column B1, assuming the data is in column A

=IF(ISBLANK(A1),"",CONCATENATE("Q-",A1))

try CTRL H- and find Q- and replace with text but this will keep the numbers

alternatively here's a macro which will do that (you will need to change the range("A1:A15"):

Sub Workbook_BeforePrint()
    Dim r As Range
    Set r = ThisWorkbook.Sheets(1).Range("A1:A15")
    For Each cell In r
        If IsEmpty(cell) Then
        ElseIf LCase(Mid(cell.Value, 1, 1)) = "q" Then cell.Value = "Midas"
          Exit For
        End If
    Next
End Sub

well I was able to use this formula to do what I needed it to:

=SUBSTITUTE(FA14, FA14, "Midas")

I wanted to take out the whole cell that contained any text and replace it with a generic term.  The substitution formula I found online is what helped me, I am not sure this was the correct way to do it but I achieved what I needed.  If anyone else can post a way to do it without using another column field, I would still be interested.

I was hoping to use something like that but I got an error

Doc1.doc

I couldn't get the macro to work.

Are you using Excel 2010, and your question title "Excel 2001" is a typo?

I just tested this on 2 boxes running Win7 SP1 64bit and Office 2010 SP1 32bit. If a single cell is selected, it does the whole sheet. If more than 1 cell is selected, it works only on the selected range. Mine seems to be doing exactly what you need yours to do, I don't know why yours isn't working.

Are your cells containing "Q-*" just data cells, or is the "Q-*" the result of a formula?

Your data are actually formulas. Use:

Find: ="Q-*"
Replace: Midas

Does not work.  Microsoft Excel cannot find any data to replace.  Check to see if your data and formatting are correct.

TYPO, it is 2007

lee555J5,

The cells contaning Q- are just text.

I got it to work!

What made it work? What fixed it?

i tested the macro and that worked so strange why it didn't work on yours....well done atleast its solved

I think why it originally didn't work for either (macro included), was because the file was protected.