what you're looking for is SUBSTITUTE:
=SUBSTITUTE(A2,"Author","Authoring")
Will substitute Author for Authoring without messing with everything else
Answer from DarkAjax on Stack OverflowIs it possible to use replace in formulas?
Excel: replace part of cell's string value - Stack Overflow
Breakdown of the REPLACE formula in Excel
Replace function on Excel
Hi guys, as of the title I'd like to know if it is possible to find and replace parts of a formula, rather than the cell content. For instance, if I have a compiled sheet where in row A1:A20 the formulas are referred to a set of sheets, but I want to change the reference to another set, is it possible to find and replace the reference appearing in the formula instead of changing manually every formula in every cell?
what you're looking for is SUBSTITUTE:
=SUBSTITUTE(A2,"Author","Authoring")
Will substitute Author for Authoring without messing with everything else
What you need to do is as follows:
- List item
- Select the entire column by clicking once on the corresponding letter or by simply selecting the cells with your mouse.
- Press Ctrl+H.
- You are now in the "Find and Replace" dialog. Write "Author" in the "Find what" text box.
- Write "Authoring" in the "Replace with" text box.
- Click the "Replace All" button.
That's it!
When you need to swap out part of a text string in Excel whether you’re correcting a typo, updating a name, or restructuring data, the REPLACE function is one of the simplest and most powerful tools at your disposal. It lets you pinpoint exactly where in a text string you want to make a change, how many characters should be replaced, and what you want to put in their place. By understanding each of its four components, you can transform text dynamically and precisely, without manually editing every cell. Below is a clear breakdown of how each argument works and how they come together to reshape your text exactly the way you want.
The formula: REPLACE(old_text, start_num, num_chars, new_text)
old_text - This is the text string that you want to modify. It could be a cell reference or even manually entered like "Dua Lipa" within the formula.
Example: =REPLACE(A1, ....) or =REPLACE("Dua Lipa", ....)
start_num - This is the position (number) of the first character in old_text that you want to replace.
For example, if you set start_num to 2 in "Hello", the replacement starts with the second character, "e".
Example: =REPLACE(A1, 2, ....) or =REPLACE("Dua Lipa", 2, ....)
num_chars - This specifies the number of characters you want to replace, starting from start_num.
For example, if num_chars is 3 in "Hello" (and start_num is 2), the characters "ell" will be replaced.
Example: =REPLACE(A1, 2, 3, ....) or =REPLACE("Dua Lipa", 2, 3, ....)
new_text - This is the text that will replace the specified characters in old_text.
For example, if new_text is "ey", and you're replacing "ell" in "Hello", the result will be "Heyo". This new_text can be the same length, shorter or longer than the number of characters you are replacing.
Example: =REPLACE(A1, 2, 3, "ey") or =REPLACE("Dua Lipa", 2, 3, "ey")
Example Results:
If A1 had Hello in the cell, "Heyo"
Using the second example, it would return, "DeyLipa".
https://www.youtube.com/watch?v=TL3nJ1cN2Tk
TL;DR
REPLACE lets you swap part of a text string in Excel by choosing where to start, how many characters to overwrite, and what to put in their place.
Use it like: REPLACE(old_text, start_num, num_chars, new_text) to quickly fix typos, update names, or reshape text without manual editing.
https://www.youtube.com/watch?v=TL3nJ1cN2Tk
Hello Excel Enthusiasts,
I need some help on a specific replace function I would like to do on an excel assignment. There are several links in my raw data and I want to replace the text with a label that is more easy to visualize.
Example- https://zoom.us/registration/27374shdyesi
I would like to make a replacement for the specific text “https://zoom.us” to change the ENTIRE cell to “zoom registration”. The issue with replace is that it only changes the text that was put into the command, so it changes to “zoom registration/registration/27374shdyesi”
Your help is much appreciated!
Excel 2021
Find and Replace (CTRL + H)
Search for ~*
Replace with whatever.
Cheers!
If you are doing this in code, you can look for CHAR(42), which is the numeric code for the * symbol.
Depending on how you do it, you should even be able to use the CHAR(42) in a worksheet formula.