Excel Forum
Similar Content
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
Macro: This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week ...
Convert Column Number to Letter Using a Formula in Excel
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
SUBSTITUTE() Function - Change Specific Text within a Cell in Excel
Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course




Excel Magic Trick 519: SUBSTITUTE & REPLACE Functions


See how to
REPLACE requires a starting position in the text string and the number of characters from the start position and it will replace those characters. For example:
=REPLACE("rad",1,2,"Shre") = Shred
SUBSTITUTE will look for a string (text) and replace it with what you tell it. For example:
=SUBSTITUTE("rad","ra","Shre") = Shred
See how to:
1.Use REPLACE to Extract Last Name
2.Use SUBSTITUTE and LEN to count spaces
3.SUBSTITUTE to replace characters in a sku number
4.REPLACE to Insert text into a text string
5.REPLACE and VLOOKUP to Insert text into a text string
6.SUBSITUTE to replace second instance of a sub-text string





Got a Question? Ask it Here in the Forum.