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

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�