Iserror() not seem to be working

**jocarexcel** · 03-13-2018, 07:04 PM

I have a client that posts social media items on a social media platform. He gets reports in Excel that show him a report including copy, clicks, shares, posts, date, time etc. The copy field sometimes contains the hyperlink that directs the reader to more information. He wants a way to filter out the hyperlink and just get the words of the copy.

I wrote a formula for him but the iserror() doesn't trap the error. =IF(B1=1,MID(A1,30,LEN(A1)-29),IF(B1>1,MID(A1,1,B1-2),IF(ISERROR(B1),A1,"")))

The copy is in column A and the hyperlink can be at the first of the copy, at the end of the copy, or no hyperlink at all.

My formula tries to capture all three:

First I find where the hyperlink starts using the find function: =FIND("http",A1,1) and plug the location number in column B.

Then I have a formula that copies the copy without the hyperlink to column C,
=IF(B1=1,MID(A1,30,LEN(A1)-29),IF(B1>1,MID(A1,1,B1-2),IF(ISERROR(B1),A1,"")))

So
if the link is at the first of the copy, column B will have the value of 1 and I use the mid function and copy everything after the link to column C.
(The links are always the same length, so I am able to use hard numbers in the mid function)

if the link is at the end of the copy, column B will be greater than the value of 1 and I use the mid function and copy everything before the link (excluding the space before link) to column C.

This is where I run into problems.
If the copy does not contain a link, column B has #value! in it and I want to copy the entire cell from column A into column C. I googled #value! and learned if I use the iserror() trap, I should be able to trap the error and copy the whole copy to column C but it only copyies #value!, not column A.

What am I doing wrong? I have included an attachment that shows examples of what I am doing.

Thanks for your help.
CJ