Trim vs Replace vs Substitute function in Excel
=Trim(text)
Removes all spaces from the string except spaces between words.
=Replace(old_text,start_num,num_chars,new_text)
Replace function will replace part of a text string, based on the number of characters you specify, with a different text string.
- old_text = This is the text we wish to replace some characters.
- Start_num (required argument) — The position, within old_text, of the first character that you want to replace.
- Num_chars (required argument) — This is the number of characters to replace.
- New_text (required argument) — The replacement text.
=Substitute(text,old_text,new_text)
It will replace one or more text strings with another text string. The function is useful when we wish to substitute old text in a string with a new string.
- Text (required argument) — This is the text or reference to a cell that contains the text for which we want to substitute characters.
- Old_text (required argument) — The text we wish to replace.
- New_text (required argument) — The text we want to replace the old_text with.
Difference between SUBSTITUTE and REPLACE Functions
The SUBSTITUTE function is quite similar to the REPLACE function. However, SUBSTITUTE replaces one or more instances of a given text string whereas REPLACE replaces text in a specified position of a supplied string.
Excel File Attached: https://docs.google.com/spreadsheets/d/18mXjLqWsz4MxUvBBBF_d1dXxekQqoPrL/edit?usp=sharing&ouid=111095223928322304587&rtpof=true&sd=true