As processing strings with formulas is kind of fun just to add some more formulas for this.
1. Concatenating strings
As you probably already know Excel’s worksheet functions do not support string concatenation for an array. e.g. the following:
does not work. Typical suggestion in these cases would be to use a UDF (or an add-in such as MOREFUNC.XLL with the function MCONCAT).
For only small ranges you could still do it with worksheet functions. The problem is how to deal with blank cells if you want to use a delimiter between each filled cell and without checking each single cell for blank.
So let’s have a look at the following example:
Formula used (in cell F1): =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1),” “,”-“)
As you can see this formula concatenates (using the hyphen as delimter) the strings in column A-E and skips blank cells. This formula has one restriction though: The concatenated cells are not allowed to contain spaces. So if you need a more complex solution best to use VBA for this.
2. Splitting strings in characters
The following is probably more a fun application but if you want to split a string value into its single characters you can achieve this with the following formula:
Formula used (in C1): = MID($A1,COLUMN()-COLUMN($C1),1)
and copy this formula across.