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,

Enter this as an array formula (Ctrl+Shift+Enter)

=TEXTJOIN(",",TRUE,TEXT(A7:D7,"0.00"))

This will now show all numbers with 2 decimal points.

Hope this helps.

Answer 3

Thank you Ashish,

What you gave me somewhat worked.  For some reason when you use the "TEXTJOIN" along with the "TEXT" function it doesn't allow the "TEXTJOIN" to work correctly.   When using "TEXTJOIN" (see below left) you see what it does to whole numbers.  When I used "TEXTJOIN with the "TEXT" function (upper right) it looks at empty cells and places "0.00" in the lineup even though the "TEXTJOIN" part of the function is "TRUE" indicating to skip empty cells.  It does though give me the zeros after the decimal point that I am looking for.  Note: ignore the comments in red; they have nothing to do with what I am trying to show you.  This example is only a short one.  My file I have has about six empty cells in the columns after every three cells with data.  Also I have in quotes the actual function/formula that is located in the blue color cells below each box.  Any ideas on this one?

Answer 4

Hi,

My guess is that in cell C9 there is some space or some other special character.  Delete that special character.

Answer 5

Ashish,

That is not the problem.  I looked at that before.  What you see in my example is results after I did a "clear contents" on Cell C9.  And as you can see the function "TEXTJOIN" does what it is suppose to do and skip that empty cell.  But the function that has the "TEXTJOIN" with the "TEXT" function embedded in it, sticks in a "0.00" in the lineup.

 When I place a "blank-space" in cell C9; the results from the "TEXTJOIN" function shows "0,15.01,30, ,60.04,90,180.38" ; and the result with "TEXTJOIN with TEXT" function shows, "0.00,15.01,30.00, ,60.04,90.00,180.38".  So it still seems like the "TEXTJOIN with TEXT" function is ignoring the skip empty cell argument of "TEXTJOIN".

 I wonder if there is some other method that could takes the results in the cell of "TEXTJOIN" and copy to/update the string to display the decimal point and two places when it sees a whole number?