Limitation on MID function? - very large string

[Aaron Z]

I have a cell that contains several thousand characters of fixed-length data.
I've been able to parse out various values using a file layout that I refer
to for location and length within this cell (the thousands of characters are
for several hundred fields, so I don't want to parse them manually...
especially since I need only a handful of values). I don't seem to be
getting data however for characters past character 332. The data cell (A1)
is able to hold all of the 2,700 characters, but the MID function seems to be
unable to parse out characters beyond a certain character location.. even if
its just 2 characters from say characters 1,000 - 1,001.

Is there a limitation to how many characters the MID function will look
through?

I have thousands of these cells, all with data in the same character
positions, so making this formula work would be helpful.

Thanks in advance.

 

[Bernard Liengme]

I would try Data | Text to columns to extract the bytes I needed.
Try it and come back for more
best wishes

 

[JE McGimpsey]

MID() is able to operate on a string up to 32767 characters long.

What exact MID formula are you using?

What does LEN() return for your cell?

Does LEFT(<cell>,1001) return 1001 characters?

 

[Aaron Z]

Thanks, the LEN function helped me discover a mistake I made. Even though
the characters appear to be 2,500 in length (I copy and paste the characters
into Word and then do a 'Word Count' from the tools menu... which stated I
had approx. 2,500 characters) the LEN function showed that I had actually
closer to 6,000 characters. I was parsing from the wrong area which turned
out to only have spaces (assuming the string to be only 2,500, I was
backtracking from there to find the data). I modified the MID function to
include the full length of the 6,000 character string and it now works
correctly.

Great idea for checking with LEN. I'm not sure I could have discovered
otherwise that the cell had much more data than the 2,500 it was showing.

The MID function I was using was the standard one:
=MID(A3,5463,3) -- I had 2,500 where instead I needed 5463.
This works correctly now.

Thanks again.

 

[Peo Sjoblom]

If you want to disregard trailing, leading or extra spaces in-between you
can use TRIM

=MID(TRIM(A3),etc