Laurianne
asked on
Mystery using IF with Concatenate using MS Excel
I have a string here which is supposed to concatenate the days with the word Day1 Day2 etc... in between but only if they exist. Here is the string:
=IF(M2="","",CONCATENATE(I F(M2<>"",C ONCATENATE ("Days1:", M2),""),IF (N2<>"",CO NCATENATE( " Days2:",N2),""),IF(O2<>"", CONCATENAT E(" Days3:",O2),""),IF(P2<>"", CONCATENAT E(" Days4:",P2),""),IF(Q2<>"", CONCATENAT E(" Days5:",Q2),""),IF(R2<>"", CONCATENAT E(" Days6:",R2),""),IF(S2<>"", CONCATENAT E(" Days7:",S2),""),IF(T2<>"", CONCATENAT E(" Days8:",T2),""),IF(U2<>"", CONCATENAT E(" Days9:",U2),""),IF(v2<>"", CONCATENAT E(" Days10:",V2),"")))
When I do this, if the cell is blank it still puts in the word Days: I've tried several combinations with no result. Can anyone point out what I am missing?
Thanks!
=IF(M2="","",CONCATENATE(I
When I do this, if the cell is blank it still puts in the word Days: I've tried several combinations with no result. Can anyone point out what I am missing?
Thanks!
Are you sure it is a blank cell and not a space in the cell (i.e. spacebar was used)
ASKER
From what I can tell it is just a blank. The data came from a database. I might try testing both though...
I entered the formula as is into a cell and it was blank, as soon as I started entering numbers in M2:V2 I got data, only if I inserted a space in a cell did I received an extras Days text string...
I would test with =LEN(M2)>0
Cheers,
Kris
Cheers,
Kris
Have you considered a VBA Function...
This will do what you need in the background.
It will allow the range to be summarised to be more or less as required.
The code can easily be modified to change the format and delimiter used.
But this is all based upon whether you feel VBA is the way to go.
DaysSumary-Function.xlsm
Function DaysSumary(ByVal DaysToSumarise As Range) As String
Dim DayArray()
For x = 1 To DaysToSumarise.Count
If Not DaysToSumarise(1, x) = vbNullString Then
ReDim Preserve DayArray(x - 1)
DayArray(x - 1) = "Days" & x & ":" & DaysToSumarise(1, x)
End If
Next x
DaysSumary = Trim(Join(DayArray, " "))
End Function
This will do what you need in the background.
It will allow the range to be summarised to be more or less as required.
The code can easily be modified to change the format and delimiter used.
But this is all based upon whether you feel VBA is the way to go.
DaysSumary-Function.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This version uses only one concatenate, I think it is easier to read:
=CONCATENATE(IF(LEN(M2)=0, "","Days1: "&M2),IF(L EN(N2)=0," "," Days2:"&N2),IF(LEN(O2)=0," "," Days3:"&O2),IF(LEN(P2)=0," "," Days4:"&P2),IF(LEN(Q2)=0," "," Days5:"&Q2),IF(LEN(R2)=0," "," Days6:"&R2),IF(LEN(S2)=0," "," Days7:"&S2),IF(LEN(T2)=0," "," Days8:"&T2),IF(LEN(U2)=0," "," Days9:"&U2),IF(LEN(V2)=0," "," Days10:"&V2))
I used LEN to test, so you will see if you have space or any other invisible characters somewhere.
If you have unwanted space characters, TRIM is a good solution, so you can use TRIM(M2)<>"" within the IFs.
You can also consider CLEAN for removing nonprintable characters.
Cheers,
Kris
=CONCATENATE(IF(LEN(M2)=0,
I used LEN to test, so you will see if you have space or any other invisible characters somewhere.
If you have unwanted space characters, TRIM is a good solution, so you can use TRIM(M2)<>"" within the IFs.
You can also consider CLEAN for removing nonprintable characters.
Cheers,
Kris
ASKER
That did exactly what I wanted! Thank you!
Laurieanne, I am not sure that this is an entirely fair distribution of points.... it seems afterall that I pointed out the exact cause of the problem at the beginning.... just because I didn't re-write the formula to adapt to the bad behaviour of having spaces in cells, doesn't mean my suggestion was wrong.... but it's totally up to you...
ASKER
Very sorry but I did try your solution yet it still printed the word "Days" when there were no values (I tested for spaces too). I tried the LEN(M2)>0 you suggested and "Days" still appeared). Tried several variations too with no luck. Again I am very sorry.