Excel formula replace text This Excel tutorial explains how to use the Excel REPLACE function with syntax and examples. If you know the text to be replaced, use the SUBSTITUTE function in Excel. Description REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. If you know the position of the text to be replaced, use the REPLACE function. In this tutorial, learn how to use Excel REPLACE function. Aug 3, 2024 · In this article, we discussed how to replace a text of a cell based on condition in Excel. For example, the following formula replaces the "123" at the end of the text with "456": =REPLACE("ABC123",4,3,"456") // returns "ABC456". Feb 9, 2023 · This tutorial demonstrates how to use the REPLACE Function in Excel and Google Sheets to replace text in a cell. If you want to get only this in excel using the formula: STQ8QGpaM4CU6149665!, use this: =REPLACE(H11,SEARCH(J11,H11),LEN(J11),"") H11 is an old character and for starting number use search option then for no of character needs to replace use len option then replace to new character. Copy and paste this table into cell A1 in Excel In the above example we can use the SUBSTITUTE function to replace all instances of apples with cookies using the following formula. Here we explain how to use REPLACE formula with examples and downloadable excel template. The REPLACE function can also be used to remove a part of the string by simply swapping the text with an empty string. Use it when you know what text you want to change, but you don’t know (or care) where it appears in a text string. Mar 13, 2023 · Several ways to find and replace multiple words, strings and characters in Excel using formulas, macros or special tool. Optionally, you can specify which instance of text to replace by providing a number. To completely Oct 12, 2024 · Learn how to replace words in Excel using formulas like SUBSTITUTE, REPLACE, and SEARCH for efficient text replacement in your data. Sales and New_Sales. May 3, 2025 · To replace one delimiter with another in a text string, you can use a formula based on the TEXTSPLIT function and the TEXTJOIN function. Mar 24, 2025 · Learn how to replace words in Excel and Google Sheets using formulas, Find and Replace, shortcuts, and tips to handle multiple values and characters. Also, when the edits span across multiple worksheets or workbooks, you’re easily looking at hours of lost time. Aug 30, 2024 · The REPLACE function substitutes a text string with another text string. functions for this. Syntax REPLACE (old_text, start_num, num_chars, new_text) The REPLACE function syntax has the following arguments: Old_text Required. These examples can help you decide which function will work best for your worksheet. Oct 5, 2023 · This tutorial explains how to find and replace values within formulas in Excel, including an example. See how to use the REPLACE function with text strings, numbers and dates, and how to nest several REPLACE or SUBSTITUTE functions within one formula. Mar 21, 2023 · Learn how to use Excel Find and Replace to search for specific text or numbers in a worksheet or in all open workbooks. Aug 3, 2024 · The article focuses on how to use find and replace formula in excel. This function is extremely useful for editing and updating data in your spreadsheet without the need for cumbersome manual text adjustments. Oct 22, 2023 · To replace or substitute all occurrences of one character with another character, you can use the SUBSTITUTE function. The REGEXREPLACE function allows you to replace text from a string with another string, based on a supplied regular expression ("regex"). Jul 6, 2024 · In this article, you will learn about how to Find and Replace data in Excel using the Find and Replace tool, functions, shortcuts etc. Jun 23, 2024 · The article shows how excel convert formula result to text string. The REPLACE function is a text function; therefore, the return value is always in text format. Use this function to replace parts of a text string. Syntax SUBSTITUTE (text, old_text, new_text, [instance_num Replace formulas with their calculated values When you replace formulas with their values, Excel permanently removes the formulas. The SUBSTITUTE function is used to replace occurrences of a specific substring within a text string with a new substring. Whether you use the SUBSTITUTE function for simple text replacements, the REPLACE function for position-based replacements, or more advanced techniques like array formulas and VBA, Excel provides the tools you need to clean up and format your data efficiently. I am replacing this to blank. Feb 17, 2025 · The Substitute function in Microsoft Excel replaces a specific text in a text string with a different text. Learn syntax, examples, and advanced techniques for precise text replacement in your spreadsheets. Its syntax comprises four essential parameters, each playing a crucial role in defining the replacement process: Aug 25, 2025 · Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. The second parameter, start_num Jun 16, 2024 · Method 2 – Insert the REPLACE Function to Find and Replace Multiple Values in Excel In the following picture, the column with the New Text header will display the modified texts. Jun 16, 2024 · In this article, we will go through how to replace special characters in Excel using different features, built-in and custom functions. Sep 2, 2022 · As the name says, the REPLACE Function in Excel replaces a specific set of characters in a text string with a new text string. No doubt you can think of several obvious use cases for this, but perhaps we can also give you a few not-so-obvious situations in which REPLACE might come in handy. What is REPLACE? The REPLACE Function Jan 2, 2024 · The Excel REPLACE function is a dynamic text formula that replaces specific content within a given string. Jun 2, 2025 · The SUBSTITUTE function is a way to perform a find‑and‑replace with a formula. These functions offer flexibility for managing and cleaning data, whether it’s preparing reports, analyzing trends, or simply tidying up data entries. Includes numerous formula examples in Excel and VBA (WITH PICTURES). We define the starting position, the number of characters we want to remove and the new replacement text. Jul 26, 2024 · Excel SUBSTITUTE Function Explained with Syntax and Example Formulas. Learn all the steps here + a bonus method (sample file included). This article describes the formula syntax and usage of the REPLACE function in Microsoft Excel. Text in which you want to replace Feb 22, 2023 · Learn how to use Excel's SUBSTITUTE function for both Mac and PC. By mastering these Sep 22, 2025 · Wondering how to replace words in Excel? Keep reading! Manually editing one or more words in text strings can be a hectic task, especially if there are many such edits are required. Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or text string. It can be used in macro code as a VBA function, entered through the Microsoft Visual Basic Editor integrated with MS Excel. May 9, 2023 · The REPLACE function can substitute an old character with a new one at a specific position within a text string. This article describes the formula syntax and usage of the SUBSTITUTE function in Microsoft Excel. They allow you to modify text strings by replacing specific characters or substrings. Sep 18, 2025 · Use Microsoft Excel SUBSTITUTE and REPLACE functions to change the contents of a text string, by removing old characters, and adding new characters. By default, SUBSTITUTE will replace all instances of a text string with another text string. This allows you to do things like replace all dashes with spaces, or vice versa, replace a word or a number in a cell, and even to replace all instances or a word, number, or character in a cell or a list of cells. This article describes the formula syntax and usage of the SUBSTITUTE function in Microsoft Excel. The Microsoft Excel REPLACE function replaces a sequence of characters in a string with another set of characters. You can either locate the search item for reference, or you can replace it with something else. Select the cell or range of cells that contains the formulas. If you accidentally replace a formula with a value and want to restore the formula, select Undo immediately after you enter or paste the value. You can choose to replace just the one found or replace all. Text in which you want to replace Mar 18, 2023 · The REPLACE function replaces the specified number of characters from the string based on the starting position with the mentioned text, string, or value. The Replace function replaces part of a text string based on the number of characters May 23, 2023 · Struggling with replacing data in Excel? Here's how to use the REPLACE function and effortlessly modify your spreadsheet content. Once the form pops open, select Look In "Formulas" and then type in the Find what and Replace with values. In the example shown, the formula in C6 is: =SUBSTITUTE (B6," ","-") Jan 4, 2025 · Excel is an incredibly powerful tool for data analysis and manipulation, and as users dive deeper into its array of functions, two particular functions stand out for their ability to modify text strings: Substitute and Replace. Step 1: Open MS Excel Open Microsoft Excel spreadsheet in which you want to use Find and Replace feature. The Excel Replace function is designed for use with text strings and returns a text string. Syntax The REGEXREPLACE function replaces strings within the provided text that matches the pattern with replacement. Introduction When working with spreadsheets in Excel, it is often necessary to replace specific text within a cell based on a certain condition. You can search by rows and columns, search within 4 days ago · Learn how to extract text after the last character in Excel using RIGHT, SEARCH, SUBSTITUTE, TEXTAFTER, REPLACE, XLOOKUP, and custom VBA. Learn them, download the workbook and practice. It's handy for making text substitutions or modifications in Excel. Mar 13, 2023 · The tutorial looks at how to delete everything before/after a specific character or between two characters by using Excel formulas, custom function, Find and Replace, or a special tool. g. Apr 10, 2017 · 0 You can use find and replace (Ctrl + Shift + H or on the Home Ribbon, click on the Find & Select button under editing). You can include wildcard characters such as question marks, tildes, and asterisks, or numbers in your search terms. It requires four arguments: the original text string, the position of the character to replace, the number of characters to replace, and the new character to replace the old one. How to Check if a cell contains one of many texts in Excel: To find check if a string contains any of multiple text, we use this formula. Jul 7, 2024 · This article shows the 5 suitable examples of Excel Formula to Replace Text with Number. were used. See how to use wildcard characters in search criterial, find cells with formulas or specified formatting and more. The REPLACE function uses the position of text in the string to replace. In the example shown, we are replacing commas with line breaks. Change or replace text in a cell with other text - you can replace a single character, numbers, letters, etc. Excel TEXT, CONCAT, CONCATENATE, copy & paste feature etc. There are two functions in Excel that allow you to replace text in a text string. Instead, you can use different Excel functions, user interface commands, and automation tools to This article describes the formula syntax and usage of the REPLACE function in Microsoft Excel. A guide to Excel REPLACE Function. Jul 9, 2012 · You can use the Conditional Formatting to replace text and NOT effect any formulas. Jun 16, 2024 · This tutorial takes a deeper look at how to substitute multiple characters in excel based on their location with another based on content. Aug 17, 2023 · This tutorial explains how to replace text in Excel if a cell contains specific text, including an example. The Excel REPLACE function is a Text function thathelps users replace an old text character or an entire string with a new set of characters or strings. Download the practice workbook, and enjoy learning! The Excel REPLACE function replaces a portion of text in a string by using a position number. You can remove a section of characters from a string and replace it with new text. Replacing Text using the SUBSTITUTE and REPLACE Functions Watch the video and/or follow the tutorial below. The SUBSTITUTE Function was introduced in MS Excel 2007 and is available under Excel Text functions. Download the workbook and practice. Description Substitutes new_text for old_text in a text string. Jun 9, 2025 · Master Excel's REPLACE function with this comprehensive guide. As a worksheet function, it can be written as a part of a formula in a worksheet cell. Jul 7, 2024 · Here, we have discussed 4 methods for excel replace text in selected cells using shortcut, wildcard characters and Find and Replace features. Therefore, if you attempt to use the replace function with a date, time or a number, you may get unexpected results. e. The syntax of the REGEXEXTRACT function is: Sep 2, 2024 · Final Thoughts Replacing characters in an Excel cell is a common task that can be accomplished in several ways. . SUBSTITUTE allows you to specify the content you want to replace – for example you may want to replace all hyphens in a product code with a space. We used FIND, REPLACE, SUBSTITUTE, VLOOKUP etc. This task can be accomplished using Excel's built-in functions and formulas to efficiently manage and manipulate data. The function will replace part of a text string, based on the number of characters you specify, Oct 22, 2025 · The Excel REPLACE function replaces characters specified by position in a given text string with another text string. Apr 4, 2025 · The SUBSTITUTE and REPLACE functions are powerful text manipulation tools in Excel. The REPLACE function in Excel allows you to change specific parts of a text string. Apr 23, 2024 · The Microsoft Excel REPLACE function is a powerful tool designed to swap part of a text string with a different text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. It replaces part of a text string with other, based on the number of specified characters Aug 4, 2024 · SUBSTITUTE, a text function in Excel workable from the version Excel 2007, returns existing text with new text in a string. Syntax SUBSTITUTE (text, old_text, new_text, [instance_num Mar 21, 2023 · The tutorial explains the Excel REPLACE and SUBSTITUTE functions with examples of uses. Sections The REPLACE function is categorized under Excel TEXT functions. It’s optimal for various tasks, from data cleaning to formatting dynamic text. Jan 29, 2025 · How to Find Text and Number in Excel The Find function is perfect when you need to locate specific text, numbers, or formulas in your workbook. Jul 15, 2024 · In this article, you will get to know the easiest ways to replace text in Excel formula. It will replace one or more text strings with another text string. To do this, we use the SUBSTITUTE () function in Excel. Jun 14, 2024 · The Excel formula replace function follows a simple syntax that involves three key components: the text or cell reference you want to modify, the value you want to replace, and the replacement value. Simply go to the Rule's format where you will see Number, Font, Border and Fill. The Excel REPLACE function is used to replace a certain length of text from a given string.