TextJoin Returns Number as Text

Status
Not open for further replies.

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
444
Is vba code an option? If so, something like the following may help

VBA Code:
'
    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

Active Member
Joined
Feb 16, 2017
Messages
380
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

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
'
    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

New Member
Joined
Apr 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.

1618899368273.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,178
Members
418,284
Latest member
LaurentT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top