Limitation on MID function? - very large string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aaron Z - 12 Sep 2007 19:46 GMT

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.

Reply to this Message

Bernard Liengme - 12 Sep 2007 20:06 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have a cell that contains several thousand characters of fixed-length
>data.
[quoted text clipped - 19 lines]
>
> Thanks in advance.

Reply to this Message

JE McGimpsey - 12 Sep 2007 20:38 GMT

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?

> 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
[quoted text clipped - 13 lines]
>
> Thanks in advance.

Reply to this Message

Aaron Z - 12 Sep 2007 22:02 GMT

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.

> MID() is able to operate on a string up to 32767 characters long.
>
[quoted text clipped - 21 lines]
> >
> > Thanks in advance.

Reply to this Message

Peo Sjoblom - 12 Sep 2007 22:05 GMT

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

=MID(TRIM(A3),etc

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 48 lines]
>> >
>> > Thanks in advance.

Reply to this Message