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 ...
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 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.