2007 cell character limit

[Jeff]

If I am in an xml text editor and I copy a block of text that is long, when I
try to paste it into a cell in an Excel 2007 sheet, it tells me the text is
too long and that I have to make it shorter or cancel.

The text is long, but not 32,000 characters. It seems to be limiting me to
255 characters.

Any help in copying long text into cells would be appreciated.

Thanks.

 

[Steve]

Jeff,
I had always thought that it limited text entry to no more than a dozen
characters.
While I'd read we could do 32k integers, my experience has been once of
being truncated on lengthy sets.
I just tried to enter a few hundred numbers into a single cell and when I
was done, my entry had been truncated to 12 characters. Well, let me restate
that-- I input a decimal number which was truncated. I now put in a whole
number/integer and it did not get truncated-- 1327 characters.
I then copied it, and it worked fine-- 1327 characters were copied/pasted.

I then copied and pasted that 1327 characters multiple times until it maxxed
out. I was stopped at 26162 characters. I could easily copy/paste those into
succeeding cells with the standard ctrl+c and ctrl+v.

I did not reach the 32k integers that I'd heard we could do.
hth. If so, please click yes.
If not, ignore me.

 

[Jeff]

Thanks, Steve. Actually, I have decimal points in the long entry.
Specifically, I am entering a string of coordinates that form a polygon for
use in Google Earth. Each point is a latitude and longitude. Each is a
decimal number and separated by a comma.

99.9999999, 1.11111111; 88.8888888, 2.2222222....

 

[Steve]

Hi JEff,
Well, I just input 379 characters, using your general strings of lat/long.
I can copy them no problem.
Something I was reminded of with your use was that I'd once used Matlab for
something comparable. Thankfully I was doing the data entry, but one
scientist I was working under at that time had set up a mult-dimensional
array that was for both lat/long, and altitude. The region was from 38 north
to 42 north, and from 117 west to 122 west. I.e., a 4 degree by 5 degree
grid.
It set up a really nice topographical map we used from that for the work I
was doing at the time.
My point is that instead of entering all your data points in a single cell
for each polygon, wouldn't it work better for individual points in an x, y, z
arranged array?

Ok, I just tried what I did before and I got more input this time than
before-- over 28,000.
I was able to copy and paste no problem here.

 

[Jeff]

Steve, your reply is some real encouragement. I'm not familiar with the
process of entering an array into a cell, but I could figure it out.

Instead, however, since you're being so helpful, maybe I could e-mail you
the file from which I am copying coordinates. You can see what's in there
and tell me why it will not copy. It is basically an xml file. I have an
xml editor I am using, called xml marker 1.1. It's free and pretty darn good.

I copy everything in the <coordinates></coordinates> tag set and try to
paste in an Excel cell, where I get my error message.



I am new to this discussion group and not sure how to send an e-mail to you.

 

[Steve]

Jeff, before we do that, let me ask you this.
is there some material that I can read on the use of xml code in google
earth? I.e., is this the source for the kml files?
I understand xml, so that's not the issue.
But I've never made an xml polygon file for earth before.
Then, I'll look at the xml marker you mention.
I use the Custom UI Editor for my xml ribbon in Excel 2007.
Beyond that, I never had the need for xml.

 

[Jeff]

Yes, it is just plain-old xml that GE uses to process certain tags a certain
way,

The "Google KML Reference Guide" is pretty good, but I doubt you'd need to
be familiar with it at all to just open the file, copy the coordinates
between the <coordinates></coordinates> tag pair, and try to paste into an
Excel cell.

 

[Steve]

Hi again Jeff,
Ok, I looked at the reference material for google earth. It looks pretty
straight forward.

In looking at the coordinate elements, I'm seeing some fairly short
coordinate strings.

How precise are you trying to get?

I know that when I go to the Weather Service page, and check my house, or
office location, the coord's only go to 7 decimal places for each- lat/long,
and zero decimal places for the altitude. In fact, the altitude only goes to
a 5-10 ft accuracy.
Thus, you should be able to do no more than 40 characters per cell (lat- 8
decimal places, for 13 characters ; long- 8 decimal places for 13 characters,
and altitude- 5 digits) and that'll give you a very precise location for each
grid point.
Each grid point would be for one cell.

XML code sample from
http://code.google.com/apis/kml/documentation/kmlreference.html#gxlatlonquad

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2"
xmlns:gx="http://www.google.com/kml/ext/2.2"> <!-- required when using
gx-prefixed elements -->

<Placemark>
<name>gx:altitudeMode Example</name>
<LookAt>
<longitude>146.806</longitude>
<latitude>12.219</latitude>
<heading>-60</heading>
<tilt>70</tilt>
<range>6300</range>
<gx:altitudeMode>relativeToSeaFloor</gx:altitudeMode>
</LookAt>
<LineString>
<extrude>1</extrude>
<gx:altitudeMode>relativeToSeaFloor</gx:altitudeMode>
<coordinates>
146.825,12.233,400
146.820,12.222,400
146.812,12.212,400
146.796,12.209,400
146.788,12.205,400
</coordinates>
</LineString>
</Placemark>

</kml>

 

[Jeff]

Yes, for that example. But I am mapping political districts and state lines.
They follow rivers, etc. Here is the polygon for Alabama. I don't know how
many characters that is, but it can't be 25,000.

Ouch! Upon posting, it says it's about 60,000! I guess I'm out of luck.
I apologize for leading you down this road. I saw the string, but never in
my wildest dreams thought it could be over 10k.

If you know of a clever work-around, I'm ears.

I can't see breaking it into multiple columns, because concatenating them
will cause the same problem.

 

[Steve]

Hi again....
without actually seeing what you have, I can't do much more.
you can reach me at (e-mail address removed)

Part of what I'm thinking at the moment is to extract the data using a macro.
However, I need to see the xml file.
Or even a sample of it.
Now, I may not be the best person to help from that point. If I find I can't
help, I'll point you over to the developer newsgroup for excel. There are
some really good programmers over there who can help develop some code to
extract from a text file.

In fact, can you save that xml file as a text file? I know that Excel has a
text import tool that can arrange the data in the file as needed. From that
point, a macro is an easy tool.

 

[Jeff]

Yeah, I'm with you, Steve. I think some coding would be required to pluck
everything but the coordinates out into the spreadsheet. I could edit
everything but the coordinates and then, with some additional coding, go back
and replace everything in the kml, except the coordinates. These coordinates
come from the US Census maps; it's not like I want to change them.

Thanks so much, Steve. I really appreciate your help.

 

[Steve]

For now, I'd say save the file as a text file.
Once you save it as a text file, clear out the stuff you don't need, and
just save the data in the coordinates section.
Use the excel import text tool. Configure as a text delimited using commas,
and then import the file.
Once you get in to excel, look for specific elements-- you'd previously
said-- <coordinates>....</coordinates>
This should allow you to then arrange the data into the x, y, z elements for
each cell using a macro.
Again, without seeing a sample of the file, I can only guess from this point
forward.
I still have this memory of what Matlab did, and it was indeed impressive--
a variation on what Arc does.