Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find/Replace text in Excel 2001

Posted on 2012-03-15
16
Medium Priority
?
207 Views
Last Modified: 2012-03-16
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
Comment
Question by:slatefamily
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 2
  • +2
16 Comments
 
LVL 11

Expert Comment

by:netballi
ID: 37724641
Hello,

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

=IF(ISBLANK(A1),"",CONCATENATE("Q-",A1))
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37724663
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
 

Author Comment

by:slatefamily
ID: 37724679
well I was able to use this formula to do what I needed it to:

=SUBSTITUTE(FA14, FA14, "Midas")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:slatefamily
ID: 37724692
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
 
LVL 13

Accepted Solution

by:
lee555J5 earned 2000 total points
ID: 37724723
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
 

Author Comment

by:slatefamily
ID: 37724832
I was hoping to use something like that but I got an error

Doc1.doc
0
 

Author Comment

by:slatefamily
ID: 37724835
I couldn't get the macro to work.
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 37724993
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37725032
Your data are actually formulas. Use:

Find: ="Q-*"
Replace: Midas
0
 

Author Comment

by:slatefamily
ID: 37725144
Does not work.  Microsoft Excel cannot find any data to replace.  Check to see if your data and formatting are correct.
0
 

Author Comment

by:slatefamily
ID: 37725145
TYPO, it is 2007
0
 

Author Comment

by:slatefamily
ID: 37725150
lee555J5,

The cells contaning Q- are just text.
0
 

Author Closing Comment

by:slatefamily
ID: 37725162
I got it to work!
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 37725192
What made it work? What fixed it?
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37730029
i tested the macro and that worked so strange why it didn't work on yours....well done atleast its solved
0
 

Author Comment

by:slatefamily
ID: 37730287
I think why it originally didn't work for either (macro included), was because the file was protected.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question