Excel Forum

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.