TextJoin Returns Number as Text

[Pulsar3000]

Hello:

The formula in cell T2 returns a number as text. Column I has a date (it matches a date between two other dates based on the name of a place in a different sheet) and seems to be the issue but I'm not sure. I used the Value function and adding a zero at the end and it made the result numeric but it no longer returned the multiple values that it found.

I also concatenated the values in a helper column, copy/pasted them as values, and tried to reduce the function but the matching didn't work. Lastly, I copy pasted Column I with the dates but that too didn't work.

Question: How can I get the formula to return multiple values in one cell be numeric?

Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))

Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded

 

[Coding4Fun]

Convert numbers stored as text to numbers in Excel - Microsoft Support

When numbers are actually stored in cells as text, problems with calculations or sorting can occur. Read this article to learn how to fix this problem.

support.microsoft.com

try wrapping it in value()

 

[johnnyL]

Is vba code an option? If so, something like the following may help

'
    Range("T2:T").Select        'specify the range which suits your purpose
'
    With Selection
        Selection.NumberFormat = "$#,##0.00"    ' 2 decimal point precision
        .Value = .Value
    End With

 

[Coding4Fun]

TEXTJOIN requires a UDF, I would think VBA is OK

Either option should work , @johnnyL posted the VBA solution I posted the Formula solution.

 

[Pulsar3000]

@Coding4Fun As mentioned in my OP, the Value function breaks the function but thank you for the suggestion.

 

[Coding4Fun]

It may be where its being placed but if the VBA solution works cheers

 

[Pulsar3000]

Is vba code an option? If so, something like the following may help

'
    Range("T2:T").Select        'specify the range which suits your purpose
'
    With Selection
        Selection.NumberFormat = "$#,##0.00"    ' 2 decimal point precision
        .Value = .Value
    End With

@johnnyL VBA code is not an option.

 

[Pulsar3000]

It may be where its being placed but if the VBA solution works cheers

@Coding4Fun:

When the function in Column T finds multiple values, the Value function can't convert it to a number. I think there's a way to put these values separated into different columns but I don't know how many matches it will find so I wouldn't know how many helper columns to have.

 

[Fluff]

How can I get the formula to return multiple values in one cell be numeric?

You can't. The fact that you have multiple values means they will be text.

 

[johnnyL]

@ Pulsar3000, have you tried multiplying the result in the cell by 1?