Problem with REPLACE function

❓ Questions & help
1

Hi all,

I’m having a problem with the REPLACE function; or perhaps I don’t understand how it’s supposed to work.

I have a test worksheet with a column of words.

I would like to change all occurrences of the word “try” to “fry,” but keep all the other words as is. So I enter =REPLACE(“try”, 1, 1, “f”) in the cell in the first row:

When I apply the function, the program correctly replaces “try” with “fry” in the first row:

But when I pull the little box in the corner of the cell down to apply the formula to the rest of the column, it changes ALL the words to “fry”:

Screenshot (18)
Screenshot (18)1280×1024 112 KB

The same thing happens if I instead use a column formula, except it puts “fry” in all the column’s cells up to 100.

Any help gratefully received!

Nancy R.

2

Hi Nancy,

Actually, it isn’t working as you are passing ‘try’ as the first argument in the formula. Instead you could use a cell reference which has the text that needs to be replaced and then do autofill.
Now, the formula will be evaluated based on the values in the cell references.

Eg: REPLACE(A1,1,1,‘f’), where A1 cell has ‘try’ as the value

You could also use SUBSTITUE formula to achieve the same
Hope this helps!!

Thanks,
Shivani.

2 Likes
3

If we want to update the value w.r.t specific string then, please use the formula as per the screenshot.

image
image832×409 33.2 KB

(Or)

We can evaluate the same by using “column formula” datatype column as well.
IF(A=“try”,REPLACE(A,1,1,“F”),A)

image
image1481×620 62.8 KB

1 Like
4

Hi Shivani,

Thanks for your reply.

Using the cell reference doesn’t work. (See screenshot 19.)

But SUBSTITUTE works perfectly!

I really appreciate the help.

Nancy R.

Screenshot (19).png
Screenshot (19).png1280×1024 114 KB

1 Like
5

Hi Kiran,

Thanks for your reply.

I was wondering whether this would require an if-then construct, but Shivani’s SUBSTITUTE suggestion is simpler.

I appreciate the help!

Nancy R.

2 Likes
6

P.S. to Kiran,

Sad to say I can’t use the very cool Column Formula type, because my spreadsheet must be converted to tsv form and the only way I’ve figured out to do that is to download to Excel and then convert to tsv in Excel (Or rather in LibreOffice Calc, which is what I use instead of Excel.) Because Excel/Calc doesn’t support Column Formula, it does not convert.

If Spreadsheet.com could convert directly to tsv I would be very happy.

Thanks again.

Nancy R.

1 Like
7

Hi Nancy,

Glad to hear that it worked!!

Regarding TSV support for export, please drop in a message under the suggestions sections

Thanks,
Shivani.

1 Like
8

I’ll do that, thanks.