Separate cell value into user specified size chunks with space seperator. How to?

[JugglerJAF]

I'm looking for a custom function that will take a long character string (for example: 1234ABCD5678EFGH in cell A1), and split it into chunks in column B, and have each chunk separated by spaces (but all in the same output column) with the number of characters in each chunk to be specified by the user.

For example, if the chunk size was 4, then the B1 would be "1234 ABCD 5678 EFGH", but if the chunk size was 7 then the B1 would be "1234ABC D5678EF GH".

The length of the initial text string could vary and as you can see in the "7" example above, it's not always going to divide into equal sized chunks.

I've been scratching my head over how to do this. I'm sure there is a way, but I can't think of it.

Any suggestions?

 

[VBA Geek]

try this

Excel 2010
	C
1	1234ABCD5678EFGH
2	1234 ABCD 5678 EFGH
Sheet7

Cell Formulas
Range	Formula
C2	=sep(C1,4)

Function Sep(S As String, chunk As Integer) As String
Dim tmpStr As String
Do Until chunk > Len(S)
    tmpStr = tmpStr & Left(S, chunk) & " "
    S = Mid(S, chunk + 1)
Loop

If Len(S) Then
    tmpStr = tmpStr & S
Else
    tmpStr = Left(tmpStr, Len(tmpStr) - 1)
End If
Sep = tmpStr
End Function

try this

 

[JugglerJAF]

That's absolutely perfect. Thank you so much!!