Concatenating to ignore (blanks)

[NJS1982]

Hi, does anyone know how I can concatenate to ignore blanks?
My formula is this:

=IF(AW6="",E6,CONCATENATE(E6," This funding has contributed towards a ",AW6," - ",AX6))

However, AW6 is blank and it is producing a result of the contents (a sentence) of cell E5 plus "(blank) - (blank)" at the end....

 

[Joe4]

It looks to me that if AW6 was really blank, it should be returning whatever is in E6 and nothing else.
Are you sure that AW6 is really blank, and doesn't have something like a space in it?
Maybe you need:
=IF(TRIM(AW6)="",E6,CONCATENATE(E6," This funding has contributed towards a ",AW6," - ",AX6))

 

[NJS1982]

Well, AW6 has a formula in it, but the result is blank...
PS - I meant to refer to E6 in my OP, not E5.

 

[Joe4]

Please post the exact formula in AW6.

 

[NJS1982]

=IF(B6="LOTT",VLOOKUP(A6,'Lott Pivot 1'!$A$4:$Z$250000,MATCH("Facility",'Lott Pivot 1'!$A$4:$U$4,0),0),IF(B6="EXCH",VLOOKUP(A6,'EXCH Pivot 1'!$A$4:$T$100000,MATCH("Facility",'EXCH Pivot 1'!$A$4:$T$4,0),0),0))

 

[Joe4]

It looks like you formula will return the results of a VLOOKUP, which will either be a value of #N/A,or it will return 0.
In either case, it will never return a "blank".
So what exactly do you want it to check AW6 for? "0", "#N/A", something else?

 

[NJS1982]

Yes, it is a VLookup, looking at text fields, not numeric. AW6 either shows the text lifted from elsewhere, or it is blank (because the source of where the VLOOKUP is pulling data from is a blank cell, but the lookup_value has an entry, so there is no #N/A return).

Currently if AW6 is blank, the response is '[content of cell E6] This funding has contributed towards a (blank) - (blank).'

As AW6 is 'blank' on the face of it, I want to try and show just the '[content of cell E6]'.

If AW6 were not blank and had text in it that I could see, I would expect it to say '[content of cell E6] This funding has contributed towards a [content of cell AW6] - [content of cell AX6].'

 

[Joe4]

Yes, it is a VLookup, looking at text fields, not numeric. AW6 either shows the text lifted from elsewhere, or it is blank (because the source of where the VLOOKUP is pulling data from is a blank cell,

I don't think it is really blank. If the column it was returning was really blank, it would actually return a 0.
However, if the column that it is returning has spaces, it would return spaces (which maybe appear to be blank, but really aren't).

To prove this, in one of these instance where AW6 is supposedly returning a blank, trying putting this formula somewhere:
=LEN(AW6)
If AW6 was TRULY returning a blank, this formula will return a 0.
If this formula is returning anything else other than a 0, it is, in fact, NOT actually blank.

It is important to note the difference between blank (nothing) and a space (something).

If is it just regular blank spaces, see if this formula returns a 0:
=LEN(TRIM(AW6))
If it does, then those blank spaces can be handled to do what you want by using the formula I posted for you up in my first reply.
If it does not return a 0, then you have some other special, non-visible characters in there (which is often seen from data that comes from the internet or some non-Excel source).

 

[NJS1982]

Weird; the 'blank' cell of AW6, if I enter your =LEN(AW6) formula, shows the number 7?! I have no idea why? All blanks seem to be '7'?

 

[NJS1982]

Ahhh, I just got why it is 7...( b l a n k ) is 7 characters!

So, my fixed formula is:

=IF(AW5="(blank)",E5,CONCATENATE(E5," This funding has contributed towards a ",AW5," - ",AX5))

Thank you!