0

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?

1

1 Answer 1

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.

2
  • 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, 2023 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, 2023 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.