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

lookup_value argument is more than 255 characters
problem the lookup_value argument is more than 255 characters
vlookup lookup value length
this value must be less than or equal to 100 characters long excel
vlookup with long strings of text
excel mid function between two characters
vlookup longer than 255
excel left function for numbers

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!


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

Evaluating formulas greater than 255 characters, The function is implicitly public, takes an implicitly ByRef parameter that has no other than getting the Excel devs to lift the 255-char limitation in the object cell - but this function will only ever return an error value to Excel; VBA code can merrily consume it though. \begingroup Thanks Mat, this is great! 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.


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.

Returning strings longer than 255 characters from UDFs results in , CallUDF (used by xlwings udfs) returns a 2D Vari than 255 from a pure VBA UDF results in a #VALUE error when called in excel. allows string >255 length to make it to Excel successfully. thanks for the great tool! Re: Solution or alternative to Excel MATCH 255 character limit. You can't use wildcards with that formula, you will need to use other functions like LEFT,RIGHT or ISNUMBER(SEARCH()) e.g =INDEX(MyRange,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,MyRange)),0),0)) to find the A2 value within the cell. or


VBA Evaluate Method 255 character limit, Re: VBA Evaluate Function 255 character limit . Hi, . There are Using Excel 2007 Value = EvalArgStr [color=darkgreen]'Output Evaluate Argument String[/ color] [color=blue]Let[/color] wks. Possibly due to how VBA is working .a) and .b ) are one and the same thing… UNIQUE function returning 0. Nice thing is that this comparison uses all of the cell's contents, not just first 255 characters. Second array is all the row numbers. Multiples the two arrays to get a single array of 0's and the 1 row number (True = 1, False = 0). The MAX then looks at the array and returns the row number for where your data is at.


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

Excel formula: Match long text, The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT functions to parse and compare text. When these formulas are recalculated in earlier versions of Excel without opening the linked workbooks, characters beyond the 255-character limit cannot be returned. What it means When formulas in a workbook are linked to other workbooks that are closed, they can only display up to 255 characters when they are recalculated in Excel 97-2003.


255 Character limit on VLOOKUP, To create a hash function, you may use the ideas from https://stackoverflow.com/ questions/125785/password-hash-function-for-excel-vba. When working with strings in VBA, use vbNewLine, vbCrLf or vbCR to insert a line break / new paragraph. This article will also discuss how to use use the line continuation character in order to continue a statement in your actual VBA code on a new line.


Strings and Manipulations, LEFT, Returns the leftmost characters from a text value, LEFT Find length of a string. Functions: To use an Excel function in VBA prefix with Application. Most of 192 to 255 are characters of the Latin1 Font are in other languages with the exceptions of 215 and 247, so you can refine the above to suit your Thanks, Joe.


Maximum number of characters in a cell, Thanks - 66355. Hi, How can i get a maximun of characters in a cell? Is that possible in Excel? Thanks For exmple, if in the cell is the number 3.14 LEN() on it returns 4. You can type upto 255 characters in a cell (including spaces). To find length (characters entered) of that cell, you can use function´┐Ż