Split Text

[Guest]

Hi all

i'm trying to split a text, actually a real easy task, excel provides the
split
function! But what if I want Excel to use each character as a substring??
i.e. "Hello" ==> "H","e","l","l","o"

Is this possible?

I want to make a function which returns me a string, with a space between
every character: "Hello" ==> "H e l l o"
If I could split up the string, i could join it again, and put a space
between each
character. Possibly there's another / better solution for my problem, if so,
i would be real glad if you could tell me

Thanks a lot for everything

Cheers Carlo

 

[NickHK]

Carlo,
Something like this :

Public Function InsertSpacesInText(argInput As Variant) As String
Dim i As Long
Dim Tempstr As String

For i = 1 To Len(argInput)
Tempstr = Tempstr & Mid(argInput, i, 1) & " "
Next
'remove last SPACE
InsertSpacesInText = Left(Tempstr, Len(Tempstr) - 1)

End Function

You should add code for dealing with embedded spaces if required.

NickHK

 

[Guest]

Hi Nick

Thanks a lot for your answer, the same approach i started after
posting this thread

i did it without a tempstring:

'-----------------------------------------
Function InsChr(Space_Text As String, Character As String) As String

For i = 1 To Len(Space_Text)
Space_Text = Mid(Space_Text, 1, (i - 1) * 2 + 1) & Character &
Mid(Space_Text, i * 2)
Next i

InsSpace = Left(Space_Text, Len(Space_Text) - 1)

End Function
'-----------------------------------------
Embedded Spaces will be treated as characters.

Thanks for your time

Cheers Carlo

 

[Ron Rosenfeld]

Hi all

i'm trying to split a text, actually a real easy task, excel provides the
split
function! But what if I want Excel to use each character as a substring??
i.e. "Hello" ==> "H","e","l","l","o"

Is this possible?

I want to make a function which returns me a string, with a space between
every character: "Hello" ==> "H e l l o"
If I could split up the string, i could join it again, and put a space
between each
character. Possibly there's another / better solution for my problem, if so,
i would be real glad if you could tell me

Thanks a lot for everything

Cheers Carlo

Here's another approach using regular expressions:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=REGEX.SUBSTITUTE(A1,"(.(?!$))","[1] ")

The translation of that formula is

"(.(?!$))" Find every character except the last character

"[1] " Replace that character with itself followed by a <space>.


--ron

 

[NickHK]

Carlo,
Whilst this function will work without side effects when called from the
worksheet, if called from code the variable supplied to fill the Space_Text
argument will be changed, as you are passing by reference.
If this is not intended, you should change the signature to read ...ByVal
Space_Text As String,..., or use a TempStr
Check out "ByVal" and "ByRef" in the Help if in doubt.

NickHK