Calc: Number of strings in CONCATENATE

[lonk]

This is a sample/result that I need for cell B3 and B4:
"""FirstName""" & " " & "||" & " " & _

From Sheet1:
Cell A2 : FieldA
Cell B2 : =CONCATENATE("""""""",A2, """""""",REPT(" ",1),"&",REPT(" ",1),"""",",","""",REPT(" ",1),"&",REPT(" ",1),"_")
Cell A3 : FieldB
Cell B3 : =CONCATENATE("""""""",A3, """""""",REPT(" ",1),"&",REPT(" ",1),""",REPT(" ",1),""",REPT(" ",1),"&",REPT(" ",1),"""","||","""",REPT(" ",1),"&",REPT(" ",1),""",REPT(" ",1),""","&",REPT(" ",1),"&",REPT(" ",1),"_")
Cell A4: FieldC
Cell B4 : =CONCATENATE("""""""",A4, """"""""," ","&"," ","""," ","""," ","&"," ","""","||",""""," ","&"," ","""," ",""","&"," ","&"," ","_")

Cell B3 and B4 showed error code Err:509, is there any limitation of string number to be combined in CONCATENATE ?
And are there any more ways to substitute for " "(space) in formula of Calc ?

Locbaf_Developer_SQL_Design_FillUpGridProduct_20220627_1140_AskLO.ods (17.7 KB)

[Zizi64]

Error 509 (from the help):

509

Missing operator

Operator is missing, for example, "=2(3+4) * ", where the operator between “2” and “(” is missing.

[EarnestAl]

[karolus]

It seems its NOT a good idea to produce SQL-statements by literate Text-concatenating in CalcFormulas

[Zizi64]

CONCATENATE

Combines several text strings into one string.

Syntax

CONCATENATE(“Text1”; “Text2”; …)

From the help:

Text1; Text2; … are 1 to 30 text passages which are to be concatenated together into one string.

Example

=CONCATENATE("Good ";"Morning ";"Mrs. ";“Doe”) returns: Good Morning Mrs. Doe.

[Zizi64]

…but you can

• pre-concatenate some strings in a few helper cells
• use the & operator instead of the Concatenate function
• use the CONCAT function instead of the CONCATENATE function, because the CONCAT supports cell ranges too (see the Help.)

[JohnSUN]

Just to show @Zizi64’s idea of the helper cells.
Place three quotes in cell D1, copy the rest of the tail you want to see from the sample and paste it into cell E1. Now a very simple formula like =$D$1 & A2 & $E$1 will fill your column with all the rows you need.

Now you don’t need the REPT() function, all the spaces are already in the right places.
By the way, as far as I understand your purpose, you are overusing extra quotes, if you put a shorter version of the "" || " & _ in E1, then the final result will be the same.

[erAck]

@Zizi64

Text1; Text2; … are 1 to 30 text passages

That help is outdated. Newer help (since 7.0 already) correctly states 255 parameters.

[Zizi64]

…it was copied from the LO 6.1.6 version.