Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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.
0
slatefamily
Asked:
slatefamily
  • 9
  • 3
  • 2
  • +2
1 Solution
 
netballiCommented:
Hello,

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

=IF(ISBLANK(A1),"",CONCATENATE("Q-",A1))
0
 
wshark83Commented:
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
0
 
slatefamilyAuthor Commented:
well I was able to use this formula to do what I needed it to:

=SUBSTITUTE(FA14, FA14, "Midas")
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slatefamilyAuthor Commented:
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.
0
 
lee555J5Commented:
Do you have to have a formula or VBA? If not, use wshark83's suggestion but with a wildcard.

Hit [Ctrl-H]
Find what: Q-*
Replace with: Midas

Lee
0
 
slatefamilyAuthor Commented:
I was hoping to use something like that but I got an error

Doc1.doc
0
 
slatefamilyAuthor Commented:
I couldn't get the macro to work.
0
 
lee555J5Commented:
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?
0
 
Rory ArchibaldCommented:
Your data are actually formulas. Use:

Find: ="Q-*"
Replace: Midas
0
 
slatefamilyAuthor Commented:
Does not work.  Microsoft Excel cannot find any data to replace.  Check to see if your data and formatting are correct.
0
 
slatefamilyAuthor Commented:
TYPO, it is 2007
0
 
slatefamilyAuthor Commented:
lee555J5,

The cells contaning Q- are just text.
0
 
slatefamilyAuthor Commented:
I got it to work!
0
 
lee555J5Commented:
What made it work? What fixed it?
0
 
wshark83Commented:
i tested the macro and that worked so strange why it didn't work on yours....well done atleast its solved
0
 
slatefamilyAuthor Commented:
I think why it originally didn't work for either (macro included), was because the file was protected.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now