Separating long sequences of characters by space

[zevlacab]

Hello,
I have 50 to 100 sequences of 200 letters long or more in a column of Excel. I'm trying to add a space every 10 letters such as:
QWERTYUIOPASDFGHJKLZXCVB...etc
into QWERTYUIOP ASDFGHJKLZ XCVB...etc
Formulas I tried stopped after 10 characters or self-formulas replaced letters by space. Please help. Thanks. H.

 

[ChrisOswald]

Is a User Defined function all right?

Public Function AddSpaceEveryN(MyString As String, mySpacer As Long) As String
    Dim i As Long
    Dim outStr As String
    For i = 1 To Len(MyString)
        outStr = outStr & Mid(MyString, i, 1)
        If Int(i / mySpacer) = i / mySpacer Then
            outStr = outStr & " "
        End If
    Next i
    AddSpaceEveryN = outStr
End Function

To install your UDF, press Alt-F11 to enter the Visual basic Editor, go to Insert menu, select module. Select the module and paste the code in.

To use the code, (assuming the initial text is in cell A1), use the formula

=AddSpaceEveryN(A1,10)

Changing the value 10 will change the spacing interval.

 

[shg]

Or similarly,

Public Function InsertPad(sInp As String, sSep As String, iChr As Long) As String
    Dim i           As Long
 
    InsertPad = sInp
    For i = (Len(sInp) \ iChr) * iChr + 1 To iChr + 1 Step -iChr
        InsertPad = WorksheetFunction.Replace(InsertPad, i, 0, sSep)
    Next i
End Function

E.g.,

=InsertPad(A1, " ", 10)

 

[zevlacab]

Hello, I have tried both modules and it's working fine. Fantastic! Thanks. H

 

[Marcelo Branco]

Nice function shg4421 !

I was trying a different solution using a function to concatenate a range or array, but it would be more complex and would require an array formula.

It works but its much more complicated. When i saw your nice and simple solution...

May i ask you a favor,to explain this part?
(Len(sInp) \ iChr)

all the best

M.

 

[shg]

a\b = Int(a/b) after a and b are rounded to Longs

So if the string is 43 characters long and the spacing is 10 characters, (43\10)*10 + 1 = 41, the position where the first separator is inserted.

 

[Marcelo Branco]

Tks shg4421.

I didnt know that in VBA a\b = Int(a/b) if a and b Long

Now i'll try to fully understand your function.

M.
ps: by the way, follows my solution

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

array-formula (Ctr+Shift+Enter) in B1
=aconcat(IF(1*RIGHT(ROW($X$1:$X$200),1)=1,MID(A1,ROW($X$1:$X$200),10)&" ",""))

all the best

M.

 

[Peter_SSs]

I haven't tested all the suggested solutions but some may need an additional check as I suspect the OP does not want an extra 'separator' at the end if the original string length is an exact multiple of the 'separator distance'.

Another UDF to consider.

Function AddSpaces(ByVal s As String, Pos As Long, Optional Sep As String = " ") As String
  If RX Is Nothing Then CreateRX
  Multiple = (Len(s) / Pos = Int(Len(s) / Pos)) And (Len(s) > 0)
  With RX
    .Pattern = "(.{" & Pos & "})"
    .Global = True
    s = .Replace(s, "$1" & Sep)
    AddSpaces = Left(s, Len(s) - IIf(Multiple, Len(Sep), 0))
  End With
End Function

A couple of usage examples. B1 is copied to B2 and B3 is copied to B5.

Excel Workbook
	A	B
1	QWERTYUIOPASDFGHJKLZXCVB	QWERTYUIOP ASDFGHJKLZ XCVB
2	abcdefghij	abcdefghij
3	CVG	CVG
4
5	ABCDEFGHIJABCDEFGHIJ	ABCDEFGHIJ, ABCDEFGHIJ
Insert Spaces

 

[Marcelo Branco]

I haven't tested all the suggested solutions but some may need an additional check as I suspect the OP does not want an extra 'separator' at the end if the original string length is an exact multiple of the 'separator distance'.

Hi Peter,

I should have wrapped my formula with TRIM to clean up the final string.
You are right.

M.

 

[Peter_SSs]

I should have wrapped my formula with TRIM to clean up the final string.

TRIM would be fine if the separator is a blank - which is what the OP wanted. I note though that the function you have provided allows for an optional separator so if something else was used, TRIM wouldn't be sufficient.