The Substitute function in Excel is a powerful tool for text manipulation, enabling you to replace text in a string in a flexible and precise manner. Whether you’re standardizing data, cleaning up imported information, or preparing reports, this function simplifies the process and enhances the clarity of your data. By mastering the Substitute function in Excel, you can streamline workflows, ensure data consistency, and improve overall productivity. Embrace this versatile function to elevate the accuracy and readability of your spreadsheets.
- What is the Substitute function?
- Find and replace multiple values.
- Convert feet and inches to inches and feet.
- Remove the line break function with the substitute.
- Syntax of remove line break function.
- Differences between SUBSTITUTE and REPLACE.
1. What is Substitute function?
Located under Excel Text functions, the SUBSTITUTE Function was first made available in Microsoft Excel 2007. One or more text strings will be swapped out for another text string. When we want to replace outdated text in a string with fresh text, the function comes in handy.
Here’s a breakdown of how it works: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Input: The function typically requires three mandatory inputs:
Text: This is the string where you want to perform the substitution.
Old text: This is the specific text you want to find and replace.
New text: This is the text you want to use as a replacement for the “old text”.
2. Find and replace multiple values
For find and replace any kind of values you can use the find and replace icon function for get your desire values. Here are some following steps to get the values.
Step 1: Create a data table first with some information as you want.
Placed the information here.
Step 2: Here you can click the find and select tab and choose the replace option as shown below.
Step 3: After clicking the replace option then you can see this kind of menu and refer the value that you want to change like below picture. Here wanted to replace Go>Went.
Step 4: In the end, click the replace button and get the value like the below picture.
Here is the replacing result below.
3. Convert feet and inches to inches and feet
Here you can follow those steps to get the value. Those steps are
Step 1: Take some information as one is feet as well as inche.
Putted the information here.
Step 2: Add the column in A4,A5 and B4,B5. Here is the column to add the two different values. A5 is used for feet to inch and B5 is inches to feet.
Follow the below instructions here.
Step 3: Here is the formula of feet to inch. First, refer the convert formula and refer A2 and show “ft” which is feet then “in” that’s means covert feet to inch. The formula is: =CONVERT(A2,”ft”,”in”)
Applied the formula here.
Step 4: After using the formula press enter and you will get the result of Feet to inch.
Here is the result.
Step 5: Here is the formula of inch to feet. First, refer the convert formula and refer to B2 and show “in” which is inch then “ft” that’s means covert inch to feet. The formula: =CONVERT(B2,”in”,”ft”)
Used the formula here.
Step 6: In the end, click the enter button to get the result of Inch to feet.
The result is here.
4. Syntax of remove line break function.
Here’s the formula to remove line breaks using Substitute in various applications:
SUBSTITUTE(text, CHAR(10), “”)
Line breaks are represented by different characters depending on the application or system. In most cases, it’s a line feed (LF) character, which is represented by the number 10 (LF = CHAR(10)) in the character code.
The Substitute function can target these special characters and replace them with something else, including an empty string (“”).
text: This is the cell reference or the string containing the text with line breaks.
CHAR(10): This part instructs the function to find the character code 10, which represents the line feed (LF) character. “”: This is the replacement text. By using an empty string, you essentially remove the line breaks.
5. Remove line break function with substitute
While Excel doesn’t have a single function dedicated specifically to removing line breaks, here are the most effective methods you can use:
Step 1: Make a table and enter some data as you want (here taken some animal names) into the Excel sheet.
All the details have been placed on the table below.
Step 2: Add the column in C1 and C2 to get the value.
Added the column here.
Step 3: Here refers to the function of line break which is as like in the following formula. Where refers substitute formula then refers A2 column as well as CHAR(10) which is line feed. The formula is: =SUBSTITUTE(A2,CHAR(10),”,”)
Placed the information here.
Step 4: After entering the formula click on enter then you will get the result.
The result is outlined below.
6. Differences between SUBSTITUTE and REPLACE
- The substitution keyword seeks the actual text string, whereas REPLACE is determined by the position of the replaced text.
- SUBSTITUTE allows you to choose between replacing a specific occurrence of a related text string or all occurrences of that string at once. If there are multiple REPLACE expressions embedded within each other, then only one REPECT function can be executed.
The syntax of REPLACE
=REPLACE(old_text, start_num, num_chars,new_text)
The syntax of SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Application of Substitute function in excel
- Standardizing Text Entries: Use the SUBSTITUTE function to replace various text spellings or formats with a standard version, ensuring consistency across your data set. This is particularly useful for cleaning up names, addresses, or categorical data.
- Removing Unwanted Characters: Employ SUBSTITUTE to eliminate unwanted or non-printable characters from text strings, such as replacing line breaks, extra spaces, or special symbols that can interfere with data processing.
- Data Formatting: Modify data formatting within a cell by using SUBSTITUTE to change delimiters, such as commas to semicolons, or dashes to slashes in date formats, making data uniform and easier to analyze.
- Dynamic URL Creation: Create dynamic URLs for hyperlinks by replacing specific parts of a URL string stored in Excel cells, enabling the creation of customizable links based on cell content.
- Keyword Replacement for SEO: Utilize SUBSTITUTE to update or replace keywords within large bodies of text to align with SEO strategies or branding guidelines without manually editing each instance.
- Template Text Adjustments: Use SUBSTITUTE to dynamically replace placeholder text in templates with actual data, streamlining the generation of customized documents, reports, or emails from a standard template.
You may be interested:
