Using IF to selectively trim cell content

[Colin Hayes]

Hi

I have a small puzzle.

I want to say that IF A1 starts with the word 'NEW' , then trim the last
24 characters from the content and replace with a full stop. Otherwise
put A1 as is.

Can someone help some code?

Thanks.

 

[Steve]

This should work as long as your cells A1 is more than 24 characters long
otherwise you will get an error
=IF(LEFT(A1,3)="NEW",LEFT(A1,LEN(A1)-24) & ".",A1)

Regards
Steve

"Colin Hayes" wrote in message

Hi

I have a small puzzle.

I want to say that IF A1 starts with the word 'NEW' , then trim the last
24 characters from the content and replace with a full stop. Otherwise
put A1 as is.

Can someone help some code?

Thanks.

 

[Ron Rosenfeld]

Hi

I have a small puzzle.

I want to say that IF A1 starts with the word 'NEW' , then trim the last
24 characters from the content and replace with a full stop. Otherwise
put A1 as is.

Can someone help some code?

Thanks.

Try:

=IF(LEFT(A1,3)="NEW",REPLACE(A1,MAX(1,LEN(A1)-23),999,"."),A1)

Note that 999 is some number larger than the largest possible number of characters in the cell.