Add a space after each character in a cell. Excel expert Tom Urtis solved it with vba. I solved it using a formula. Let’s compare these methods.

**Tom Urtis VBA Solution**

Using a loop Tom’s code extracts each character from a cell and adds a space. This is the key part:

For xLength = 1 to Len(xStr)

mySpace = mySpace & Mid(xStr, xLength, 1) & ” “

Next xLength

A user defined function (UDF) is a function created with vba. Cell C3 refers to the contents of B3.

(pic used with Tom’s permission)

**Excel File**

Get my **Excel file** and follow along.

**My Formula Solution**

=TEXTJOIN(” “,FALSE,MID(C5,ROW(INDIRECT(“1:”&LEN(C5)))&” “,1))

The formula looks at cell C5 (has text “Friday”). Don’t forget that it’s an array formula (enter it by pressing Control Shift Enter).

**Textjoin Function**

Textjoin commonly concatenates a range of values. Here we’re using characters from a single cell.

It has 3 arguments: **Delimiter**, **Ignore_empty**, **Text1**. It helps to use the Insert Function dialog box:

**1**. select cell f5, **2**. click inside of the Formula Bar, **3**.click on ‘fx’ (left of Formula Bar)

**Text1** does the heavy lifting with 4 functions inside of it. Why?

We need to extract each character from cell C5 and add a space to it (like Tom’s code).

**Mid Function**

Inside of **Text1** we have: MID(C5,ROW(INDIRECT(“1:”&LEN(C5)))&” “,1)

Mid function also has 3 arguments: **Text**, **Start_num**, **Num_chars**.

First and last arguments are easy: **Text** is cell c5, **Num_chars** is 1.

**Row, Indirect, Len Functions**

Mid’s **start_num** argument combines 3 functions creating 6 midpoints from cell C5:

ROW(INDIRECT(“1:”&LEN(C5)))&” ” Highlight it and press F9 key (on laptop hold Fn and press F9).

You get this {“1 “;”2 “;”3 “;”4 “;”5 “;”6 “} as “Friday” has a length of 6.

Select **Text1**, press F9 to see {“F”;”r”;”i”;”d”;”a”;”y”}. When space delimiter is added you get “F r i d a y”.

**Which Solution Do You Prefer?**

Non vba Excel users will normally opt for a formula solution. However in this case my formula might scare them away and push them towards Tom’s vba udf !

These days I don’t create as much vba as I did years ago but I still find it important to study vba created by experts like Tom. It keeps my skills alive 🙂

For those that have extreme vbaphobia my formula solution is an option. When it’s broken down into little pieces it’s really not that difficult to understand. Furthermore, it’s good to learn the ROW(INDIRECT(“1:”&LEN(C5)))&” ” part as it has many useful applications.

**About Tom Urtis**

Tom has been a Microsoft Office developer and trainer for over 30 years. Tom’s amazing website is **www.atlaspm.com** He has written several Excel books and has been an Excel MVP since 2008.

I’ve learned a lot of Excel from Tom over the years. I regularly follow his twitter feed (content from his blog). I often save his Excel tip images as my laptop background pic. Another way to learn from Tom!

**About Me**

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada and work in Toronto.

This is my personal **blog** about Microsoft Excel. I also make **YouTube videos**.

This year I’m learning Power Query (Get & Transform). It’s an amazing new Excel tool! Visit my **recommended Excel training courses**!