Hitting cell character limit (I think)

pete212

New Member
Joined
Aug 20, 2012
Messages
20
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,603
Yes, there is a limit of 32,767 characters per cell (2^15 - 1). You could put the data in 2 cells like so:

Rich (BB code):
    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.
 
Upvote 0

Forum statistics

Threads
1,187,074
Messages
5,961,425
Members
438,544
Latest member
DrDoyle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top