[Solved] Calc concatenates numbers but not text

[BTFT66]

LibreOffice 7.8.6.1
Mac os 14.5

I use concatenate quite a bit. I know that a lot of functions have been rewritten. I am not sure what I am doing wrong.

I am a scorekeeper for a rec hockey league and keep stats. I am attempting something new for my league. I want to be able to produce stats on players that work well together by tracking who scored and who assisted the goal and how many times each combination of players score. I am creating a table to record every goal scored from all games and all teams.

The column marked code is a 2 digit team code. The next 3 columns are the players that scored and assisted. All goals are not always assisted so sometimes one or more of the assist columns are blank.

I am then attempting to CONCATENATE the team code and the goal and assists into one cell. as you can see in the attached file, the first line of entry appears to work, but things breakdown when empty cells are found. CONCATENATE wants to insert a zero when there is a blank cell. This is not acceptable. I tried typing a physical space but got an error message. I tried inserting the letter x but get an error message. It will accept the team code (text) but after that it will only accept numbers.

I formatted all the cells as text but that didn't help. I am inserting hyphens in between the numbers. I tried putting quotes around them but that only inserted the formula instead of the result. I am very confused as to why this isn't working.

Thank you in advance for any help or insights that you may have. This problem is under the GOALS tab of the file.

Hockey Concat.ods

(57.69 KiB) Downloaded 102 times

[karolus]

Hallo


Formula for »Goals.J2«:

Code: Select all

=TEXTJOIN("-";1;E2:H2)

LibreOffice 7.8.6.1

this Version does and will never exist… do you mean 7.6.??

[Alex1]

A minus sign gives an error message in front of text, and has no effect on a zero value. That's why it doesn't work.
Use =E2&"-"&F2&IF(G2="";"";"-"&G2&IF(H2="";"";"-"&H2)) etc.

[BTFT66]

Karolus: correct version is 7.6.2.1 I will try the textjoin function. Thank you.

Alex1: If you look at the sheet that I uploaded, I tried to put the hyphens in quotes but the formula itself was displayed instead of the concatenated string of text. I will attempt something other than a hyphen to see what happens. Thank you for that.

[BTFT66]

Okay, I tried the textjoin formula exactly as you typed it and it did not work. As I was typing it that little help window came up and it was using commas instead of semi-colons. That didn't work either. I tried the range and I tried entering each cell one by one. I tried replacing the hyphen with a space and also with a comma. Nothing worked.

I tried replacing the hyphen with a space and with a comma in the concatenate formula but it still doesn't work. I tried both of these functions using $ before the columns but not before the rown numbers and I tried them without the $ as well.

[Alex1]

It doesn't work because the cell is formatted as text. Format it as All.
Always format a cell before using it. Changing the format from text to anything else or vice versa doesn't affect the current content.

E.g. in column F some numbers were entered when the cell was formatted as numbers: F1, F6:F9, F11,
and some were already text: F2:F3, F12. You can see it when you use value highlighting: Ctrl+F8.
You can also see the difference when you don't change the default alignment.
When using the SUM function, only the blue cells are counted: SUM(F2:F12) gives 210 instead of 248.

At first you said LibreOffice, now you have OpenOffice in your signature, so I don't know what you're actually using.

[BTFT66]

Wow! That is something I didn't know but explains a lot. Also, as I create a new spreadsheet, I decide what columns will be what and put in titles. I then usually highlight the columns and format them but it will not format unused cells doing it that way. If I click on the cell next to A and above row one to highlight a sheet and format with a font and font size. Again, it will not format the unused cells. I am continuously having to change the font and font size as I go.

Thank you for this. Much appreciated.

[BTFT66]

I have a signature? LoL! I am using LibreOffice. I signed up here way back when I was using OpenOffice. That must be leftover from that.

[MrProgrammer]

At first you said LibreOffice, now you have OpenOffice in your signature, so I don't know what you're actually using.

Unpacking meta.xml in Hockey Concat.ods I see that the file was created with LibreOffice/7.6.2.1. I interpret office:version="1.3" to mean ODF 1.3.

<office:document-meta xmlns:grddl="http://www.w3.org/2003/g/data-view#"
                      xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0"
                      xmlns:dc="http://purl.org/dc/elements/1.1/" 
                      xmlns:xlink="http://www.w3.org/1999/xlink"
                      xmlns:ooo="http://openoffice.org/2004/office"
                      xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
                      office:version="1.3">
   <office:meta>
      <meta:initial-creator>Mark Bukator</meta:initial-creator>
      <meta:creation-date>2024-06-25T11:19:50.587725872</meta:creation-date>
      <dc:date>2024-07-03T11:59:02.383851438</dc:date>
      <dc:creator>Mark Bukator</dc:creator>
      <meta:editing-duration>PT12H23M15S</meta:editing-duration>
      <meta:editing-cycles>59</meta:editing-cycles>
      <meta:generator>
         LibreOffice/7.6.2.1$MacOSX_X86_64 LibreOffice_project/56f7684011345957bbf33a7ee678afaf4d2ba333
      </meta:generator>
      <meta:document-statistic meta:table-count="5" meta:cell-count="4263" meta:object-count="0"/>
   </office:meta>
</office:document-meta>

I have a signature? LoL!

Yes, I see it in all your posts. We rely on that information to develop appropriate responses for your environment. Fix your forum signature in User Control Panel → Profile → Edit Signature. I will not help further until you've done that. You should say MacOS because IOS is only for phones.

Bogus signature.gif (28.05 KiB) Viewed 3536 times