Scenario…
A Column of Cells contains a variety of text strings e.g.
A1 contains “The quick brown fox”
A2 contains “The Lazy Dog”
A3 contains “The 1812 Overture”
I wish create a formula in each Column B cell to return a substituted hyphen ("-") for any character OR (text) numeral EXCEPT for a space " ").
B1 contains “— ----- ----- —”
B2 contains “— ---- —”
B3 contains “— ---- --------”
Can anyone help please?
The functions TEXTJOIN() and CONCAT(), capable of helping with the job, are only available in LibO Versions 5.2.7 or higher in an actually usabel implementation.
The full featured function for accumulating concatenation of text is TEXTJOIN(). For your task you can use
{=TEXTJOIN("";0;IF(MID(A11;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A11);1));1)=" ";" ";"-"))}
entered for array-evaluation (with Ctrl+Shift+Enter).
The probably unreckoned part using the ROW() function is needed to access every single character one after the other. It’s the way how parts of the action during the evaluation of formulas are serialized internally. OFFSET() is used then to allow for a variable number of steps in the process (LEN(A11) here).
Also, now using the more primitive function CONCAT():
{=CONCAT(IF(MID(A11;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A11);1));1)=" ";" ";"-"))}
And, to avoid the error otherwise occurring if the input string is empty, better:
{=IF(A11="";"";CONCAT(IF(MID(A11;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A11);1));1)=" ";" ";"-")))}
In the specific case nested into the concatenating functions COLUMN(OFFSET(INDIRECT("a1");0;0;1;LEN(A11)) will create the same effect as the ROW() part.