Only do formula if cell contains something

[LondonEddie]

Hi there,

I've got the following forumla which is working fine, however I want to be able to copy it all the way down the worksheet, but only populate the cell it's in if cell C7 has something in it, i.e. not blank so that it doesn't look untidy, is this possible?

=IFERROR(CONCATENATE(C2,B7,D2,C7),"")

Thanks,
Eddie.

 

[Sektor]

This?

=IF(NOT(ISBLANK(C7)),IFERROR(C2 & B7 & D2 & C7,""),"")

 

[InaCell]

or try

=IF(C7="","",IFERROR(CONCATENATE(C2,B7,D2,C7),""))

 

[LondonEddie]

Thanks guys, that's great. Now I'm trying to be a bit more clever... The following formula isn't quite working - I'm not sure I'm completing a formula correctly.

This is basically the formula from above, but with an added IF to ensure the contents of the cell has http:// at the start, so if the link in B9 doesn't have this, add it as part of the concatenation, if it already does, then just do the concatenation. That make sense?

=IF(C9="","",IFERROR(IF(ISNUMBER(SEARCH("http://",B9)),CONCATENATE(B9,$B$5,C9),CONCATENATE("http://",B9,$B$5,C9)))

Eddie.

 

[Dannyh1]

Hi,

I don't think you need the IFERROR part, try:

=IF(C9="","",IF(ISNUMBER(SEARCH("http://",B9)),CONCATENATE(B9,$B$5,C9),CONCATENATE("http://",B9,$B$5,C9)))

 

[jasonb75]

Maybe

=if(c9="","","http://"&substitute(B9,"http://","")&$B$5&C9)

 

[LondonEddie]

Jason - that works perfectly, thanks. Now I just need to figure out what you did...!!

Wow learning excel is so much fun!!!

Thanks everyone.

 

[jasonb75]

I used & in place of concatenate, the formula is a simplified version of

=if(c9="","",CONCATENATE("http://",substitute(B9,"http://",""),$B$5,C9))

Substitute replaces "http://" in B9 with "" before concatenating it with the rest of the string, in effect this is the same as removing the second (if found) occurrence of "http://" from the string while leaving the first in place,

i.e. =IF(C9="","",SUBSTITUTE("http://"&B9&$B$5&C9,"http://","",2))

 

[LondonEddie]

Makes sense, thanks again, Jason.

 

[Scott Huish]

You could also get rid of the http:// first and then append it, then either way it will work:

=IF(C9="","","http://"&SUBSTITUTE(B9,"http://","")&$B$5&C9)