Excel VBA (UDF) Returns #VALUE! Due to 255 Character Limit

Question

I am not very familiar with VBA in Excel. I need the VBA Function text value to return regardless of character limit (if a limit is required, 560 should suffice). I have read a lot of forum posts regarding the 255 character limit and wrapping text or using different strings etc, but I don't really understand how to implement that into the code I am using below.

The situation: I have a bunch of documents (paper copies) each day which each have a 7-digit numerical ID. This is always 7 numerical digits long. I type the values without spaces or commas into cell B4, and then in cell B5 I am calling the function via =InsertChar(B4) which will separate each document ID (every 7 digits) with a comma. The below code I have copied from online does the job perfectly, where every 7 digits it will insert a comma, until cell B5 exceeds 255 characters in which case it will return "#VALUE!".

I tried formatting the cells and adding some wraptext code (which did wrap the text), but as soon as cell B5 exceeded 255 characters, it still returned "#VALUE!" instead of the numbers & commas as text.

I would be very grateful if somebody could assist. Seems like a simple fix but I am a complete newbie to VBA and don't even understand half of the code I have used. :)

NB: Office Excel version 2016 and 365.

Cell B4 (dummy data typed in manually without commas): 1187226118437011865811187335118756231187659911875423119155451191554511915546119155471191568611915687118722611843701186581118733511875623118765991187542311915545119155451191554611915547119156861191568711915686119156871

VBA Code:

Option Explicit

Function InsertChar(STR As String, Optional sInsertCharacter As String = ",", Optional lSpacing As Long = 7) As String
    Dim sCharString As String
    Dim sFormatString As String
    Dim sTemp As String
    Dim I As Long

For I = 1 To lSpacing
    sCharString = sCharString & "&"
Next I
sCharString = sCharString & sInsertCharacter

For I = 0 To Len(STR) \ lSpacing
    sFormatString = sFormatString & sCharString
Next I

sFormatString = "!" & Left(sFormatString, Len(sFormatString) - 1)

sTemp = Format(STR, sFormatString)
If Right(sTemp, 1) = "," Then sTemp = Left(sTemp, Len(sTemp) - 1)

InsertChar = sTemp


End Function

Cell B5 (correct result of the VBA function when less than 255 characters): 1187226,1184370,1186581,1187335,1187562,3118765,9911875,4231191,5545119,1554511,9155461,1915547,1191568,1191568,1187226,1184370,1186581,1187335,1187562,1187659,1187542,1191554,1191555,1191554,1191554,1191568,1191568,1191568,1191568,1191456,1191621

Cell B5 (incorrect result of the VBA function when more than 255 characters): #VALUE!

Answer 1

It looks the error is because of the limitation of characters Format function can handle.

You need to think of another approach without using Format. For example,

Function InsertChar(STR As String, Optional sInsertCharacter As String = ",", Optional lSpacing As Long = 7) As String

    Dim RestChars As String
    RestChars = STR

    Do While RestChars <> ""
        InsertChar = InsertChar & Left$(RestChars, lSpacing) & sInsertCharacter
        If Len(RestChars) > lSpacing Then
            RestChars = Right$(RestChars, Len(RestChars) - lSpacing)
        Else
            RestChars = ""
        End If
    Loop

    InsertChar = Left$(InsertChar, Len(InsertChar) - 1)

End Function

Answer 2

I copied the function into the VBA editor and attempted to run from the Immediate pane. The function falls at the following line:

sTemp = Format(STR, sFormatString)

From my tests it seems that the VBA Format function function ignores more than 257 characters in the format string. For example, the following expression

' Generates a string composed of # characters, with a length of 272
' Pass that string as a format string to the Format function
' Return the length of the formatted string
Len(Format(5, String(272, "#")))

returns 257, not 272.

When truncated to 257 characters, the generated format string from your function has a trailing comma:

!&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,&&&&&&&,

which seems to cause the problem.

---

I would suggest looping directly over the string in groups of 7 characters. Something like this:

Function InsertComma(s As String) As String
    Dim length As Integer
    length = Len(s)

    Dim result As String
    result = ""

    Dim i As Integer
    For i = 1 To length Step 7
        If i > 1 Then result = result & ","
        result = result & Mid(s, i, 7)
    Next

    InsertComma = result
End Function

Alternatively, you could use regular expressions. Add a reference to the Microsoft VBScript Regular Expressions 5.5 library (via Tools -> References...).

Function InsertCommasWithRegex(s As String) As String
    Dim re As New RegExp
    re.Pattern = "(\d{7})(?=\d)"
    re.Global = True
    InsertCommasWithRegex = re.Replace(s, "$1,")
End Function

An explanation of Pattern -- we're trying to find:

• every group of exactly 7 ({7}) digits (\d)
• the group should be captured as a submatch ((...)) -- we need this to use the Replace method
• the group is followed by another digit (\d) -- this excludes the last group in the string because it isn't followed by another digit
• but the subsequent digit should not be part of this matched result ((?=...), AKA positive lookahead)

There are multiple matches found within the string. We want to replace each match with the captured digits followed by a comma. In regular-expression syntax, that's written as:

$1,

(NB. Note that RegExp.Replace method in VBA doesn't recognize $0 as replacing with the entire match, which is why we need the capturing group; unlike, say, .NET regular expressions.)

---

VBA References:

• Format function
• Len function
• Mid function
• String function
• For...Next statement

VBScript Regex library references (can be used from VBA):

• Introduction to Regular Expressions
• RegExp object
• Pattern and Global properties
• Replace method
• How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Answer 3

Here is another solution, using .Evaluate:

Function InsertChar(rng As Range, sInsertCharacter As String, lSpacing As Long) As String
    arr = Application.Evaluate("TRANSPOSE(IF(ROW(1:" & Len(rng.Value) / lSpacing & "), MID(" & rng.Address & ",(ROW(1:" & Len(rng.Value) / lSpacing & ")*7)-6,7),"" ""))")
    InsertChar = Join(arr, sInsertCharacter)
End Function

Formula in B1, dragged down:

=InsertChar(A1,",",7)

---

_{Note1: .Evaluate takes a string holding a formula to evaluate up to 255 chars. Therefor I used a range reference instead of a string reference in the function. Now the formula would evaluate to {IF(ROW(1:31), MID($A$1,(ROW(1:31)*7)-6,7)," ")}, where VBA recognizes that we want it to be an array formula (therefor returning an array of values).}

_{Note2: We have to TRANSPOSE a 2D-array into a 1D-array to be able to use it in Join.}

Answer 4

This does not appear to have a character limit.

For your example of 7 characters

=SplitIt(A1,",",7)

Function SplitIt(s As String, sep As String, charCnt)
    Dim rSult As String, y As Long
    Dim a, b, c
    Dim Ls As String
    a = 0
    b = charCnt
    c = charCnt

    's = [A1].Value

    For y = 1 To Len(s) / c
        Ls = Mid(s, a + 1, c)
        rSult = rSult & sep & Ls
        a = a + c
    Next

    If Left(rSult, 1) = sep Then rSult = Mid(rSult, 2, Len(rSult))
    SplitIt = rSult

End Function