Hitting cell character limit (I think)

[pete212]

Hello,

I have the below VBA code that pastes data from a text file into Cell C20 if the "show data" string exists in the file:

Dim FileNum As Long, TotalFile As String
FileNum = FreeFile
Open "C:\pete.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum , , TotalFile
Close #FileNum


If InStr(TotalFile, "show data") Then Range("C20") = Mid(Split(TotalFile, "show data")(1), 2, 50560)

The problem I have is that I need 50,560 characters (shown in red). However, when I look in cell C20 some of the data is missing off the bottom. From doing a Google search it seems that I may be hitting the character cell limit in Excel which is 32,000 odd - can someone confirm that in the first instance?

If so, is there a way the last code line above can be modified so that the first 32,000 go into cell C20 and the remaining go into (for example) cell C21?

The problem I have is that within the 50560 characters I need, there is nothing unique in it that allows me to refine the search. For example, this is the first couple of lines after "show data" in my text file:

data bank 0
data bank 0

And it continues like this for 50560 characters. (I extract this data to record the values, which might not always be 0). So I cannot unfortuntely add a second line of VBA code to search the text file 'further down' if you will, as there is nothing uniqie to do so. "show data" is the only unique anchor I can use.

 

[Eric W]

Yes, there is a limit of 32,767 characters per cell (2^15 - 1). You could put the data in 2 cells like so:

    If InStr(TotalFile, "show data") Then
        Range("C20") = Mid(Split(TotalFile, "show data")(1), 2, 32767)
        Range("C21") = Mid(Split(TotalFile, "show data")(1), 32768)
    End If

However, I'd suggest processing the data in VBA without writing to the sheet. If you saved that data in a variable instead of C20, then do a

MyArray = Split(MyVariable, "data bank")

then you could just look at all the values in MyArray for non-zero values. Even if the data doesn't all look like that, you can parse it out in VBA as easily as in Excel, and faster.