Separating characters by space

[kishorkhanal]

I have following kinds of texts in a column.

MISCELLANEOUS
EQUIPMENT
JBKU348597-3
T-225
75984

I want to convert above texts with space between every character as follows:

M I S C E L L A N E O U S
E Q U I P M E N T
J B K U 3 4 8 5 9 7 - 3
T - 2 2 5
7 5 9 8 4

Please help!

 

[Andrew Fergus]

Out of curiosity, can you try the test I tried? I used the following code in a workbook module (change 'Sheet6' to suit):

Sub testSpeed()

Application.ScreenUpdating = False

With Sheets("Sheet6")
    .Range("A1:A20000").Formula = "=randbetween(-10000000,10000000)"
    .Range("A1:A20000").Value = .Range("A1:A20000").Value

    starttime = Timer
    .Range("B1:B20000").FormulaR1C1 = "=addspace(RC1)"
    Application.Calculate
    .Range("B1:B20000").Value = .Range("B1:B20000").Value
    elap1 = Timer - starttime
    
    .Range("B1:B20000").FormulaR1C1 = "=addspace2(RC1)"
    Application.Calculate
    .Range("B1:B20000").Value = .Range("B1:B20000").Value
    elap2 = Timer - starttime
    
End With

Application.ScreenUpdating = True

MsgBox "UDF takes " & elap1 & vbCr & "regexp takes " & elap2

End Sub

and the following code in a standard module:

Private regX As Object

Function addspace(inputStr As String) As String

Dim i As Long
addspace = ""

For i = 1 To Len(inputStr)
    addspace = addspace & Mid$(inputStr, i, 1) & " "
Next i

addspace = RTrim(addspace)

End Function


Function addspace2(r As String) As String

If regX Is Nothing Then Set regX = CreateObject("VBScript.RegExp")

With CreateObject("vbscript.regexp")
    .Pattern = "(.)"
    .Global = True
    addspace2 = Trim(.Replace(r, "$1 "))
End With

End Function

Since the variable declaration the gap has improved, but I'm seeing a much better performance from the UDF that doesn't use regexp.......

Andrew

 

[Legacy 68668]

Yes of-course.

Using the global variable is only reducing the access time to RegExp.

The built-in function should be faster.

 

[Andrew Fergus]

Yes of-course.

Using the global variable is only reducing the access time to RegExp.

The built-in function should be faster.

Which one - the regexp? I'm just not seeing it.......

My mistake with the previous code, the amended code should be :

Function addspace2(r As String) As String

If regX Is Nothing Then Set regX = CreateObject("VBScript.RegExp")

With regX
    .Pattern = "(.)"
    .Global = True
    addspace2 = Trim(.Replace(r, "$1 "))
End With

End Function

Andrew

 

[Legacy 68668]

What I meant was

1) UDF using global variable variable, regX, is faster than the UDF accessing each individual UDF.(With CreateObject(....thingy))

2) Your UDF, simply looping through the variable is the fastest.