Limitation on MID function? - very large string
 

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.

Limitation on MID function? - very large string
 

I would try Data | Text to columns to extract the bytes I needed.
Try it and come back for more
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Aaron Z" wrote in message
...
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.

Limitation on MID function? - very large string
 

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?

In article ,
Aaron Z wrote:

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.

Limitation on MID function? - very large string
 

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.

"JE McGimpsey" wrote:

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?

In article ,
Aaron Z wrote:

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.

Limitation on MID function? - very large string
 

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

=MID(TRIM(A3),etc


--
Regards,

Peo Sjoblom



"Aaron Z" wrote in message
...
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.

"JE McGimpsey" wrote:

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?

In article ,
Aaron Z wrote:

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.