TextJoin Returns Number as Text

Status
Not open for further replies.

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
44
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
 
That won't work when there are multiple values in the cell.

@Pulsar3000 what are you trying to do with those numbers?
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
That won't work when there are multiple values in the cell.

@Pulsar3000 what are you trying to do with those numbers?
Ahh, I see now, I failed to realize all the matches are being saved into one cell. Yes, that will be text.
 
Upvote 0
That won't work when there are multiple values in the cell.

@Pulsar3000 what are you trying to do with those numbers?
@Fluff

I'm doing two things with those results.

1. I'm trying to get those values that reflect multiple regular rates for one person, divide them by the overtime hourly rate and see if the result equals 1.5 or 2 to determine if the overtime rate is time and half or double time.

One problem I have is that they are returned as text and the other is that even if they were numeric, I don't know how to apply formulas to multiple values in a single cell that are comma separated (like the one in item 2 below) but I will create a different post to answer this question.

2. In addition, I get those same values and compare them to other rates using a min function with a max function inside to determine what hourly rate to use to calculate what we can bill someone else.
 
Upvote 0
As you have now started a new thread for this, I will close this one.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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
Back
Top