asked on # Find/Replace text in Excel 2001

Should be easy, but i'm in a crunch and need help fast.

I have a column:

PROC5 Provider

Q-67047

Q-39884

Q-22709

Q-97099

Q-60021

I want to keep all the blank rows blank, and change any field that has data (all starting wtih Q) to the text "Midas"

Help please :)

Thanks in advance.

I have a column:

PROC5 Provider

Q-67047

Q-39884

Q-22709

Q-97099

Q-60021

I want to keep all the blank rows blank, and change any field that has data (all starting wtih Q) to the text "Midas"

Help please :)

Thanks in advance.

Microsoft Excel

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

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).Ran

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")

=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.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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?

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

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.

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.

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

=IF(ISBLANK(A1),"",CONCATE