Issues with using "TEXTJOIN" function and whole numbers.

Question

Does anyone know of a way to use the "TEXTJOIN" function and be able to bring over whole numbers from a cell in Excel that is formatted to display the whole number with zeros after the decimal point.  Example:  I have a cell that has the whole number "30.00" in it, but when I use the "TEXTJOIN" function with a delimiter of ","   and read that cell along with others in the column it will only place the whole number without the decimal point and trailing zeros in the destination cell where the function string is located.  For example if I read a group of cell that have the numbers: " 24.3  45.678  30.00  55.78" (one number in each cell)  it will give me an output, with comma delimited "," in that function, at that function location that looks like :  " 24.3,45.678,30,55.78".    I have the formatting at that cell where the function is, set to "number" with 2 places after the decimal point, the same as I have set in the group of cells I am reading.   Please let me know if there is some way to easily accomplish this.  The function "TEXTJOIN" works great for what I am trying to accomplish, but  I am just having problem with whole numbers and what it does with them.

Answer 1

=TEXTJOIN(",",TRUE,IF(C6:C12="","",TEXT(C6:C12,"0.00)))

Answer 2

Hi,

I am at a loss here.  Sorry but cannot help any further.

Answer 3

Hi John,

Any updates?

Regards,

Linda

Answer 4

Well David your formula example works very well, somewhat.  It is strange in what results it gives.  It is doing what I want with the decimal point and two places after it but I am seeing issues with the formula not giving me the same results each time it is used.  There are two strange things happening when I have the results in the cell where the formula is.  When I click on the cell with the formula and run it though the "Evaluate steps" the steps show the correct numbers in the list but at the very end of the last step it changes the the result it shows.  This is after I had run a macro on the workbook and created the sheet that has this formula in.  At first I thought maybe I had a timing issue that was causing my results to vary each time I ran it but when I did that evaluate steps and saw the results change from having the correct numbers at each step  to ending with the first number in the string not showing up, I got confused on what is happening. 

       I did find out though that when the problem happens it is always the first cell read that it is leaving out of the final string that is generated.  Now another fact about this cell that it drops randomly drops is that the cell does not have a true number in it it has a pointer to another cell IE: "=C7".  So what I was trying to do was to have that number that is in cell "C7" be put in the string.  If I remove that pointer to another cell and place a real number in that cell the function works every time  correctly.  So now I trying to come up with a way to do what I need to and putting the real number in that cell instead of a pointer in that cell.  All of the other cells that are part of that string have only real numbers in their cells.   So that may be my fix but I have to figure out how to do that because the real numbers are loaded by running a macro  from a table that doesn't include the number I am pointing to.  

     But back to what I first mentioned is the confusion of that fact that when stepping through the function it starts out with the correct results but ends with that first cell missing , randomly.