Insert space after every character of a text string

Question

I'm trying to create a formula that takes a text string of unknown length and returns it after inserting a space between every character.

For example, if A1="FOO" then the formula should return "F O O".

I can successfully return my example above with the following formula, but it's long and is hard-coded for 3 character strings:

=MID(A1,1,1)& " "&
 MID(A1,2,1)& " "&
 MID(A1,3,1)

Is there a more compact method that works with strings of any length?

A chat discussion regarding this question has been created.
– Blindspots ♦
Sep 21 at 15:59 — Blindspots, Sep 21 at 15:59

Blindspots · Accepted Answer · 2023-09-21 16:35:23Z

3

I solved my problem with the following formula:

 =TRIM(
    REGEXREPLACE(
      A1, "(.)", "$1 "))

Credit to @Erik Tyler for suggesting the TRIM function which provided a much shorter approach to remove trailing spaces.

Blindspots♦

3,4114 gold badges15 silver badges29 bronze badges

answered Sep 21 at 0:57

Patrick Hennessey

436 bronze badges

Even though the question is self-answered, you can still accept it and are encouraged to do so after 48 hours if it is the best solution. You can always change your mind later if a better solution comes along.
– Blindspots ♦
Sep 21 at 17:27
1

Since we seem to share an interest in shortening our code, you can remove an additional 2 characters by removing the parentheses from the regex and changing the $1 to $0. So, your formula would become: =TRIM(REGEXREPLACE(A1, ".", "$0 "))
– Blindspots ♦
Sep 21 at 17:33

Add a comment |

1 Answer 1