CODEWITHSUNDEEP
excelformula
user3656604
user3656604

Reputation: 21

excel formula to replace or stubstitute only text that starts with a certain letter

Prior
Rlet
Rcall
Resolved

So I want Excel to find and replace all words starting with "R" in a certain column and change them all to "Resolved". It seems like a simple substitute or replace, but I can't seem to get it to work with only words starting with "R". Thanks!

Upvotes: 0

Views: 17722

Answers (2)

MASBHA NOMAN
MASBHA NOMAN

Reputation: 198

Another way ...
1. select the Range or entire column
2. press CTRL + H or find replace option from the ribbon
3. in find cell type "R*" (without quote)
4. in replace cell type "Resolved"
this will do the trick :)

Upvotes: 1

Floris
Floris

Reputation: 46365

You are asking for a "formula". That is usually something you enter into a cell. If that is the case, then if you have the list of words in the A column, you could put the following formula in cell B1:

=IF(LEFT(A1, 1)="R", "Resolved", A1)

This will test for the first letter in A1 - if it is A, it will put "Resolved" in B1, otherwise it copies A1.

Now you could copy this equation down (double click the little square in the bottom right hand corner of the cell) hide column A, and the job is done. Or you could copy all of column B, and "paste special... Values only" B onto A, then delete A.

There are other ways too. It was not clear from your question what you were really after.

Upvotes: 2

Related Questions