How can I recover large text strings Excel changed into # symbols?

I think I’d use a simple text program like Notepad to open the files and see if the URLs are stored in the file. If not, you are well and truly screwed would be my guess.

Probably one of those “you should have” comments, but…

If I ever work in local files (Word, Excel, text, etc), I use Dropbox, so I can revert back to an earlier version. It can be used as simple source control.

Can you convert the cells of the Work Sheet to HYPERLINK ? ?

Isn’t that what Excel does when the string is too long for the box?

Widen the column in the spreadsheet.

If you can’t widen the column, change the column properties to wrap text to fit. However you will then get very deep rows… it will have to be one or the other.

Could you create the file with a text editor? Sounds like you’re hitting a character limit in Excel.

It sounds like a CSV file isn’t the right format for your data. Is there a reason you need CSV files specifically?

Just googled, you can’t have more than 32,767 characters in a cell. Perhaps 1,000 URLs could hit that limit?

@6rant6 Already been there with textpad. I was, indeed, “well and truly screwed”.

@tom_g I’m adopting a multiple backup name strategy. I name each copy to indicate its stage in the process. I have terabytes of headroom in local storage, so no need to use Dropbox, but it’s the same idea.

@Tropical_Willie Not a bad idea, but in this case, no. I intended to upload the resulting spreadsheet as a CSV file to populate a database. The final solution, however, turned out to be rather close to that.

@Nullo Now I know that it is.

@CWOTUS That doesn’t help.

@downtide As with @CWOTUS’ suggestion, been there, done that, got the tee shirt. On it is a one word in big letters, FAIL! :-(

@phaedryx Yes, one several strings were in excess of 32,767 character. One was more than 2 times that. The odd thing is that only a few were over the limit, but pasting them in caused cells that were well within the limit to convert to 255 # symbols as well. It degraded data that was within the size limit. So if Excel doesn’t want to accept a paste, be careful about coaxing it.

I needed to use Excel because I had a 3 supplier databases of product IDs with 30 columns of Yes/No parameters to sort on. I needed to sort each spreadsheet 30 times, once by each column + ID to keep the results in alpha/numeric order, then copy the IDs that had a yes for a given parameter.

I pasted them into an Excel spreadsheet. But I could have built a CSV in a text editor. What I ended up doing was pasting the IDs directly into the CMS system’s “Contents” variable for each category page in question. Fortunately, the CMS system involved accepts either a space delimited list of IDs or a carriage return delimited list, so pasting directly from a bunch of rows in the ID column worked just fine. No need to save a CSV and do a database upload.

If the file handles small URLs okay, then use TinyURL or an equivalent to condense each one.

That 32,767 limit may count URL encoded characters as 4.

@CWOTUS I wish I could, but these are actually product IDs which end up forming the HTML name of the page, so becoming part of a URL. For SEO purposes, the store owner came up with keyword rich IDs, like “Ungimmicked-Fine-Tip-Sharpie-Black-box-of-12-by-Murphys-Magic-Supplies-Trick” which will become www.domain.com/Ungimmicked-Fine-Tip-Sharpie-Black-box-of-12-by-Murphys-Magic-Supplies-Trick.html. To work when uploaded that text string must be preserved exactly.

No matter. Even if it’s 1 for 1, I am still more than double the limit on some fields.

Strings in VBA are by their nature resizable. All of VBA’s string functions are used to set or retrieve portions of strings of variable length. You may find, however, that you need strings of a specific length, such as when you are writing text-based files that will be parsed by another application, or simply for alignment in list boxes.

Thanks, but I attacked it at the data level. Some of the product descriptions were truly comical combos of HTML markup and local styles. The selection of colors, incredibly huge fonts, and such was hilarious. Apparently the HTML author that did it operated under the theory that if they don’t believe you and do what you want them to do immediately, you just aren’t bellowing loudly enough. I pulled a CSV and without even trying to open it in Excel, I used the industrial strength text editor, TextPad to run a Perl Reg-Ex search that matches all HTML tags and replaces them with ””. Many problems solved in one search and replace.