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!