Find & Replace, text not found

HI Wayne,

Odd. When I added a space after BCT-STRAT then moved it off-screen, opened Find and started entering STRAT, this popped ito view after the third letter:

And with the search term completed ("strat" with no added space) the item (with a trailing space) was still 'found':

And stayed found with one space added, but was not found (expected behaviour) when a second space was added.

Numbers 3.6.2, El Capitan.

Regards,

Barry

Hmmmm…

That turned out to be a dead end.

Pasting and copying the two 'empty' lines into TextWrangler showed only a pair of spaces and a line feed on the first line, and only a line feed on the second.

Pasting the sample showing plain text into TextWrangler showed only the plain text.

Meanwhile, I've had some out-of-thread conversations with a few others regarding this issue. The 'oddity' seems to be caused by a coding issue in the original csv file.

The (red) inverted question marks are most likely null characters (ASCII character 00000000) resulting from text encoded as UTF-16 being 'read' as if they were encoded in UTF-8. "Pure" ASCII characters are all in the range 0-127, and can be expressed with eight bits (Actually fewer). In UTF-16, each character uses 16 bits. For characters in the ASCII range, this means the first 8 of these 16 characters will all be zeros. Examples (Z and z) below.

UTF-8 uses only 8 bits.

So if a text file coded as UTF-16 is read as if it were coded in UTF-8, the "Zz" string would be represented as:

00000000010110100000000001111010

from which a UTF-8 reader would extract four ASCII characters: nullZnullz with the nulls normally not visible (and taking no space), or with the null represented by a token (such as the red ¿) producing ¿Z¿n (in two colours—which won't paste here).

More detailed discussion (short article ans some useful comments) of this (and other) issues with using csv here: CSV: An Encoding Nightmare — Donat Studios

SUGGESTIONS FOR A FIX / WORKAROUND:

Tom Gewecke suggested:

I would open it in a text editor (TextEdit set to plain text, TextWrangler, BBEdit, etc) with the encoding set to utf-16 and then save it with the encoding set to utf-8.

To which I would add: …or in LibreOffice, which offers several coding systems/file formats to choose from.

Viking OSX provided the link to the article above, and this comment (detailed in the article):

Essentially, if that was an Excel exported CSV, then it is including Windows Code Page, or MacRoman characters and not UTF-8. That CSV very likely needed to be exported a certain way as explained in the article, or post-processed with the command-line utility iconv(1) before it was opened in Numbers.

The 'copy the ¿ character, paste into Find, Replace with 'nothing'' suggestion was mine, but also offered by TuringTest2 and thought of by you…

"Haven't tried it but did think about that workaround. I am more curious to know why this is occurring in the first place (i.e. why are there ASCII characters in this csv file downloaded from a trading exchange?)"

ASCII (American Standard Code for Information Interchange) is just a method of coding 128 characters, 95 of them printable, as numerical values. Ninety five was enough to cover the English alphabet (upper and lower case), the numerals, and the common punctuation marks. Later coding systems (Extended ASCII, Unicode, etc.) added more characters, but continued to use the original numbers to encode the original characters of the ASCII set. Every character in your question above is 'an ASCII character,' as is every character in this paragraph.

Perhaps you 'real' question should be "why are there UTF-16 characters in this csv file downloaded from a trading exchange?" to which the answer would be found in the linked article from Donat Studios, and the comments following the article.

Thanks for the question! It's been an interesting journey.

Regards,

Barry

Hi JD,

The empty rows should not affect Find.

Try entering BTC-STRAT into an empty cell on the same table. Is it found?

If so, select that cell, copy, then select each of the cells that 'should' contain the same text and paste. Are all found?

If this is happening on a larger scale within the same table, you might try this:

• Select ALL cells in the table. Copy.
• Open a new document in TextEdit (found in the Utiities folder in your Applications folder, or by doing a spotlight search), click in the document window, then go Edit > Paste and Match Style.
• Select all the text, and Copy.
• Return to the Numbers document and click the + sign at the upper left to add a new Sheet.
• Click the new Sheet's tab to go to it, then click (once) on the top left cell of the table that comes with the sheet.
• Paste.

Now try Find on several strings you know are in the table, and try filtering the table on dfferent keys and different columns.

If all is working well, check through the table to confirm that the data is complete, then control-click (right click) on the 'old' sheet's tap and choose Delete Sheet.

Regards,

Barry

Open a new document in TextEdit, click in the document window, then Edit > Paste and Match Style. Select all the text, and Copy.

When I did this, the document is blank, except for the one cell BTC-STRAT that I manually entered. It's as if all the text is invisible? So when I copied and pasted into a new sheet, that also just has one cell, the rest blank.

Does the text show in the box at bottom left of the window when you select (click) on a cell that should contain text?

Check the Text format inspector. You may have inadvertently set the Text colour to white, which would explain the invisibility, but not the failure to Find.

Regards,

Barry

HI JD,

The 'content can't be saved' message is fairly common on ASC, and is most likely not connected with the issue under discussion. I suspect that message just means 'I can't get in touch with the server right now,' which is similar to 'all our agents are busy right now. please stay on the line.' At least in the case of the ASC message you can carry on with what you're doing, and even send your message without having to wait for the warning to stop.

"…it requires clicking the arrow button twice to get past each letter.. which would indicate there is a space between every character?"

Not a 'space,' but it does suggest there is a character between each visible character, and that the inserted character has no width, like the accent character shäring the space occupied by the letter "a" in "shäring" in this sentence.

The inverted question mark ( ¿ ) is part of the 'extended Latin' set of characters, Unicode number U+00BF. It's also defined in the ASCII set, where it is character 191 (same number). Most common use is in Spanish, where it precedes a question (and its 'upright' version follows the question).

Here, it seems to act as a marker for an invisible character, possibly a formatting or control (function, not 'key label') character.

Have you tried selecting and copying one of the ¿ characters, then pasting it into Find and doing a (Find and) Replace All (in TextWrangler).

Can you copy a sample from the file (the same one as your screen shot shows would be good) and paste the actual text into a message, please. Two samples of the same text, one from the Numbers file, the other from TextWrangler after a Paste and Match style of the Numbers file to TextWrangler would be even better.

That would allow me (and others) a chance to take a closer look.

Do you know what application (and platform—Mac, Windows, Linux, etc) was used to create the csv file?

Regards,

Barry

The only way I could reproduce your reported problem is to add a space after "STRAT". So I typed:

"STRAT "

Here, I removed the space and just typed "STRAT"