Separating characters by space

Put this is a Standard Module - It;s a UDF - It works!!

With you example above statrting in A1 and downward to A5 -- enter into
Cell B1 =Extraspaces(A1)
and copy down


Function ExtraSpaces(Rng As Range)
Dim a As String
Dim i As Integer
Dim t As String
For i = 1 To Len(Rng)
t = Mid(Rng, i, 1)
If i > 1 Then
a = a & " " & t
Else
a = t
End If
Next i
ExtraSpaces = a
End Function

HTH

Hi

There are a couple of ways of doing this. You could either use a self-referencing formula that uses iterations or a user defined function in VBA.

A UDF in VBA (insert into a new standard module) might look something like this:

You then call that function by entering the following formula:
=AddSpace(A1)

If you would rather use a formula, then you can do this with a self-referencing formula as follows:
1) Select menu option Tools > Options > Calculation > set Iteration to 10
2) Enter the following formula into cell B1:
3) Copy this formula down as far as you want.
4) Press and hold the F9 key until all of the changes are completed.
5) You can now copy > paste special the values in column B and remove the calculation iteration.

Please note that if you complete the calculation before copying down the formula then it won't work as expected, and each formula below the first will need to be re-pasted.

Andrew

With a UDF:

Hello HotPepper

I saw you post a similar solution in another thread - can you please explain this part:

HOTPEPPER said:

.Replace(r, "$1 ")

Click to expand...

I've seen this article but the (r, "$1 ") part is not intuitive to me.

Andrew

I have put the pattern of . in a group which is what I am using in the Replace, the $1 representing group 1, so it is going to replace in r the value of the group followed by a space. Since the pattern in the group is any character . and Global is set to true, it will replace any character (the regular expression) with that character followed by a space.

Thanks for that. So the '$1' represents the group string that matches the pattern (in this case a single character) and the character(s) before the " is what gets appended between each group......right. So you could use dots or slashes or any other character......

It's quite a bit slower isn't it?

Andrew

I think it's faster than looping through the string.


.Replace(r," ")

If our replace looked like that above (and assuming global still set to true), it would replace everything that matched the pattern with a space.

but this

.Replace(r,"$1 ")

This however references the pattern from the regular expression and replaces in the string the value(s) it finds followed by a space, but yes you could use other characters.

You can also use this technique with multiple groups, a common example is this:

TheBlueCar

Here you want to put a space between lowercase letters and capital letters, so we will use two groups:


The first match is eB, but we don't want to replace the e and the B, we want to keep them, so we need to reference those groups in the Replace, so it will replace the eB with e B

HOTPEPPER said:

I think it's faster than looping through the string.

Click to expand...

I did a quick test using 20k rows of data comparing my example UDF to the regexp UDF. The test was done along the lines of that outlined in the guru book, and the rows of data contained random text of between 1 and 5 characters each. My UDF took less than a second while the regexp UDF took 20 times longer. I suspect the issue with larger data sets is the 'create object' line.....however, if it were programmed more efficiently (maybe as a sub rather than UDF) I would expect the regexp to be faster.

Cheers
Andrew

Use global variable