Free Microsoft Excel 2013 Quick Reference

How to find and replace in Excel for special characters?

I have a special character that I cannot figure out how to replace. It is a
small square that was imported into Excel from an information system and
appears all over my cells. How do I get rid of it using find and replace?


Hi,

I am wondering if anyone knows how to find and replace on Excel charts??

For example I have the following

'Sheet1' Worksheet on Workbook 1
'Results' Worksheet on Workbook 1

'Sheet 2' Worksheet on Workbook 2
'Results' Worksheet on Workbook 2

I have copied a chart from 'Sheet 1' on Workbook 1 to 'Sheet 2' on Workbook 2.

How can I replace the references on the Chart to 'Sheet 1' on Workbook 1 to 'Sheet 2' Workbook 2?

I have about 25 charts I am looking to do this for so I am looking on tips that would allow me to do this quickly...Possibly a macro which works.

Many thanks in advance.

Sagar

Hey guys,

First of all, i wanted to thank you all in advance. I've actually been scrounging information off this for quite some time. You've helped me overcome a vast number of problems i've encountered.

This one though, i couldn't find anything on, and i thought it was about time i asked you directly

I'm not very good at explaining, so bear with me.

I have 9 Excel workbooks. 1 of these is a Final sheet, the presentation sheet that get's printed and given out. The other 8 are "raw data" sheets where i've done all the calculations and made the tables.

The Raw Data workbooks are given to me each month and dropped in a folder, overwriting the previous "raw data" workbooks. Trouble is, these workbooks have a Month name in the filename. This month name is important and i cant remove it. Example:

Final.xls, 101MAY.xls, 102MAY.xls, 103MAY.xls, 104MAY.xls, 105MAY.xls, 106.MAY.xls, 107MAY.xls and 108MAY.xls.

(These would overwrite 101MAR.xls, 102MAR.xls, 103MAR.xls etc... Final.xls does not get replaced)

I've written a macro in Final.xls that copies and pastes all the relevent information from each Raw Data sheet into the corresponding Tab in Final.xls. Thus:

Code:
    Workbooks.Open Filename:= _
        "J:xxxxxxxxxxxxxxxxRaw_Data101MAY.xls"
    Sheets("Reports").Select
    Cells.Select
    Selection.Copy
    Windows("Final.xls").Activate
    Sheets("101Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("101MAY.xls").Activate
    ActiveWorkbook.Close (False)
Edit: sorry, i should have mentioned, this is replecated 8 times, once for each raw data workbook.

As you can see, this does a Copy/Paste of the entire sheet from one workbook, into the Final.xls. Trouble is, this macro would obviously not work after i've overwritten the May files with June files (101JUN.xls etc).

What i thought i could do is make a cell in Final.xls with a drop down box, with the months Jan - Dec inside. Then i could create a second macro, that will run a Find and Replace inside the First macro, using whichever entry in the drop down box is selected to Find/Replace the file names in the macro.

IE:

Drop down box; select June.
Run new macro
**New macro would need something like "If June, then use 101Jun.xls... If August then use 101Aug.xls**
New Macro finds/replaces 101MAY.xls with 101JUN.xls inside the old macro
Run old macro
Macro copies and pastes data from 101JUN.xls like before.

Does that make sense Really sorry i made it so long winded. I'm terrible at explaining things. If anyone can help, i'd be so grateful. I can obviously just do a manual find and replace in the macro, but i'm trying to make this task run as automatic as possible, with as little human interaction as i can!!

Really greatful. thanks guys...

I have a huge mailing list and I have a macro to find and replace certain critera, the goal is to seperate these addresses into 2 parts.
the street address in ADDRESS1 and the apt/suites in ADDRESS2, If it doesnt have a apt/suite # then it should be blank.

What I want to do is find "* st" and as long as "st" is the end of the string then replace with ""

for example:

A1) 526 West 26th Street, Suite 920
A2) 526 W. 26th St. Ste 920
A3) 253 W. 23th St
A4) 235 34th st Apt 3
A5) 343 lafayette st

so that after I run the macro I have in my ADDRESS2 the following:

B1)Suite 920
B2)Ste 920
B3)
B4)Apt 3
B5)

If I do a simple Find and replace of "*st" I come up with the problem of replacing parts of A2 and
im left with "e 920" which is not what I want.
I want to be able to create multiple search criterias and not just those that have " st" at the end of the
string.
Does anyone know when using a Find and Replace if there is a special character to indicate that its the end of the string?

A3) 253 W. 23th St
that way i can Find " st(Special Character to indicate end of string)" replace with ""

thanks.

I have many hyperlink with disply in TEXT in one sheet.
Then If I would like to update some path of all hyperlink in hold sheet
How to do by simply way?(some way like find and replace, as I try to use
Find but I think Find function cannot get reach in hyperlink!)
Sample
old hyperlink: https://microsoft.com/excel/question.html
new hyperlink: https://microsoft.com/msoffice/excel/question.html

kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

When one copies and pastes content from the web, all unusable graphic get
copied over to Excel and it requires to click each one (which may have
overlap or be invisible) to delete one by one. A find and replace all
function for graphics (like Word) would solve this problem.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

When one copies and pastes content from the web, all unusable graphic get
copied over to Excel and it requires to click each one (which may have
overlap or be invisible) to delete one by one. A find and replace all
function for graphics (like Word) would solve this problem.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I want to replace texts in Electrical Master template.xls using data from Light naming.xls.The second xls file containds data to find in column A and to replace with in Column B.How to use second excel file to replace data in 1st excel file?please help i dont want to search and replace manually

How to use dates and times in Excel for Mac

To help you perform complex date and time calculations, Microsoft Excel includes many built-in date and time functions. Back to the top

Hi,

I'm trying to write a macro to find and replace and currently use:

For Each cell In Selection
cell.Value = Replace(cell.Value, “January”, "February", 1, 1, vbTextCompare)
Next cell

However, using this code means that the find and replace values must remain constant. Could anybody show me how to do the same thing but instead of finding "January" as constant text, I want it to look at the range "LIST" find the text entered in cell C5 and replace it with the text entered in C6.

Does that make sense and sound do-able to anyone???

Thanks for your help!

I have a report I run that identifies lots of rows with column A having the
word filtered, and column b having garbage. I've tried a find and replace by
hitting space bar 8 times (filtered has 8 characters) and replace filtered
with a blank cell. Column B still has garbage. How can I delete any "row"
that says filtered and have the rows compress to get rid of the large void?
I'm talking about 33,000 rows being gone at one time instead of blocking and
deleting, blocking and deleting, etc.... Can anyone help with instructions
on how to delete the numerous rows of garbage in an Excel document with one
key stroke?
--
Patmarie985

How To Use Date And Times In Excel For Windows Microsoft Knowledgebase

Describes how to use the built-in date and time functions in Excel to perform complex date and time calculations. Provides some examples.

How To Use Date And Times In Excel For Mac Microsoft Knowledgebase

... 06 represents the date and time 7/8/1992 1:26:24 A.M. To help you perform complex date and time calculations, Microsoft Excel includes many built-in date and time functions.

Hi,

what I am trying to do is to write some macro code to 'Find and Replace' certain text strings in each of my work sheet and replace with the correct text e.g. the macro will look in each worksheet in turn. find the text if it exists and replace with new text.

I have tried writing the code below but it appear only to work on the active worksheet

Dim mySheet As Worksheet

For Each mySheet In Worksheets
      
Cells.Replace What:="Test Me out", Replacement:="That Seems To work !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Cells.Replace What:="Test this as well", Replacement:="That Seems To work as well !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next mySheet

End Sub

Any help would be greatly appreciated

Excel version 2003

Many thanks

Rob

I need help with finding and then replacing a symbol in my worksheet. In the
find and replace box there is no way to insert a symbol there. I have tried
to cut and paste as well as to insert the symbol in the find box. If anyone
has an idea on how to replace a bullet with a comma please let me know.
Thanks
TC

Help,
I used the below code (kindly supplied by PetLahev on this site) as a find and replace macro. It replaces the text "qu1" with "Changes Text" through out my powerpoint presentation. The code works fine in ppt2007 but not ppt2003. Half way through the code is the line; Set oTxtRng = oShp.TextFrame.TextRange and this fouls up with the message "Invalid request. This type of shape cannot have a TextRange". Help, how can I fix the code for ppt2003?
Cheers,
BennyJ


	VB:
	
 ReplaceText() 
    Dim oSld As Slide 
    Dim oShp As Shape 
    Dim oTxtRng As TextRange 
    Dim oTmpRng As TextRange 
    Dim strWhatReplace As String, strReplaceText As String 
     
     ' write find text
    strWhatReplace = "qu1" 
     ' write change text
    strReplaceText = "Changes Text" 
     
     ' go during each slides
    For Each oSld In ActivePresentation.Slides 
         ' go during each shapes and textRanges
        For Each oShp In oSld.Shapes 
             ' replace in TextFrame
            Set oTxtRng = oShp.TextFrame.TextRange 
            Set oTmpRng = oTxtRng.Replace( _ 
            FindWhat:=strWhatReplace, _ 
            Replacewhat:=strReplaceText, _ 
            WholeWords:=True) 
             
            Do While Not oTmpRng Is Nothing 
                 
                Set oTxtRng = oTxtRng.Characters _ 
                (oTmpRng.Start + oTmpRng.Length, oTxtRng.Length) 
                Set oTmpRng = oTxtRng.Replace( _ 
                FindWhat:=strWhatReplace, _ 
                Replacewhat:=strReplaceText, _ 
                WholeWords:=True) 
            Loop 
        Next oShp 
    Next oSld 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


You would need VBA for this, to open each workbook, one at a time, do the
"find and replace" and then save/close that workbook, open the next, etc
etc............

Vaya con Dios,
Chuck, CABGx3

"Glenn" wrote:

> Is it possible to do a find and replace in multiple workbooks at once. If so
> how is it done. Thankyou for all you help.

Is there a parallel way to find and replace a character and replace with a line feed or paragraph mark? [For example, in Word, you can find or replace paragraph marks (^p)]

My Title should say Excel not Word!

Dear All

Help required

I have a worksheet containing 25 columns from A to Y without headers.

1) I need to check for special characters like ; : ' " / ~ ` ( { ) } & ^ * @ in any of the columns and replace them with blank.

2) In Column R, T and Y the data will be single character only like F, S, C and i need to check whether they are in Capitals, if not, then change them to Capitals.

3) In Col S, U, V, W and X the data is DATE. It should be in DD/MM/YYYY format. If the data is not in said format it should change to DD/MM/YYYY format.

Macro required. Please help

Hello:

I have excel workbook with many sheets.
I have VBA Code for each sheet.
I need to find and replace "AAAA" to "BBBB" from all sheets.
Currently I am going into each sheet and performing Find and replace.
Is there a way, I can do Find and Replace from all sheets.

Thanks in advance for help.

RM

Is it possible to do a find and replace in multiple workbooks at once. If so
how is it done. Thankyou for all you help.

Hello
I need a function that find and replace from "," to "."
For example A1 =" aaa,bb b,b,fff" to A1=" aaa.bb b.b.fff"
I know the Ctrl + H but I need it in function
Thank you

I've seen solutions for this on this forum but nothing I can get to work on my specific problem.

I have a master worksheet that has been compiled from a range of smaller worksheets.
This data was compiled from different sources and some of the nomenclature in one of my columns is out of date, for example. "hsa-miR-122a" is now renamed "hsa-miR-122" etc

Therefore anywhere in my ID column where hsa-miR-122a is seen I'd like it to read with the updated name. I have a list of these instances that need correcting.

I have enclosed a truncated example file to try and illustrate.

I have list of the old names and their correct version in a two column worksheet "replace"

The master worksheet that needs updating is a lot larger and extends beyond column G (col G is the column in which the names need updating).

It would also be useful to have some sort of output detailing that it has been done as otherwise the manual checking would be almost as intensive as doing a host of individual find and replaces.

I hope this makes sense.

Thank you for reading and thanks in advance for any help.

_sarah

SOLVED, thank you

Hi All,

Can anyone tell me how to Find and Replace using VBA. I want to search the range A10:A30 for the text which is in cell A5 and if the search criteria is found, then replace it by the text which is in cell A6. I thought maybe using the Indirect function, but I can't get it to work.

Thanks in advance for any help.

Regards,

Bill