Find And Replace Text For Mac Excel

In this post we’re going to explore how to find and replace multiple text strings from within another string.

Excel find and replace function
  • For the “Find and Replace: Formatting” Lecture/excersises I cannot access the “format cells” option. In other words I can only do a find and replace on text/cell references. Is this a unique problem to Excel on the Mac?
  • = SUBSTITUTE(text,oldtext,newtext,instancenum) text – The original string of text. Oldtext – The text string that you wish to find and replace. Newtest – The replacement text. Instancenum – OPTiONAL. The instance number to replace. If blank, all instances are replaced.

Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text.

Copy and paste this table into cell A1 in Excel

Find and replace text for mac excel 2007
TextRevised Text
I eat apples and bananas
ReplaceMacro

In the above example we can use the SUBSTITUTE function to replace all instances of apples with cookies using the following formula.

Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. SUBSTITUTE(text, oldtext, newtext, instancenum) The SUBSTITUTE function syntax has the following arguments: Text Required. The text or the reference to a cell containing text for which you want to substitute characters.

Advanced find and replace in excel

Copy and paste this table into cell A1 in Excel

Now if we also want to replace bananas with chocolate we could do this by using a nested SUBSTITUTE formula.

Cummins pt fuel system

As we add more and more items we want to replace we need to nest more and more SUBSTITUTE functions and this will become more unmanageable. So instead we will create a user defined function in VBA to simplify this. If you want to know how to use this VBA code then read this post about How To Use The VBA Code You Find Online.

Find And Replace Text Color In Excel Mac

This user defined function takes a text element and two ranges as input.

  • strInput – this is the text you want to replace bits of text from.
  • rngFind – this is a range that contains text strings you want to find in strInput.
  • rngReplace – this is a range that contains text strings you want to replace items from rngFind with. The dimensions of rngFind and rngReplace must be equal or the function will return an error.

Copy and paste this table into cell A1 in Excel

Advanced Find And Replace In Excel

TextRevised Text
I eat apples, bananas, carrots and cucumbers

With this user defined function we can easily take care of replacing multiple texts using a simple looking formula without nesting multiple SUBSTITUTE functions.

Find And Replace Text For Mac Excel File

Where $A$6:$A$9 is a range containing the text we want to remove (apples, bananas, carrots and cucumbers) and $B$6:$B$9 is a range containing the text we want to replace them with (cookies, chocolate, cake, ice cream).