sam
sam

Reputation: 77

Substituting excel cell with a condition

I have an excel column A which contains some data. If the cell starts with </li> then the every </li> should be replaced by <u>. The formula should not replace </li> appearing any where except the first one.

Upvotes: 0

Views: 45

Answers (1)

exSnake
exSnake

Reputation: 672

Try this formula, (obv sub A1 with the cell where the text to replace are):

=IF(LEFT(A1;5)="</li>";SUBSTITUTE(A1;"</li>";"<u>");A1)

Here's a sample

But if you want to replace only the first instance in the cell who start with , you should do:

=IF(LEFT(A1;5)="</li>";SUBSTITUTE(A1;"</li>";"<u>";1);A1)

We use the "instance_num" parameter of the substitute function, to tell her to replace only the first instance, here what happens:

Second sample

Upvotes: 1

Related Questions