Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Find & Replace, text not found

Hi, I am trying to use Find & Replace to find instances of a text string, but it isn't finding them (even though they are there) in one particular sheet. Example screenshot attached showing the result "Not Found" and you can see the searched word in the second row. This only occurs in this one sheet, find works in other sheets.


Similarly, if I try to filter a column (right clicking at the top of the column and selecting a text string to filter by), the results show none.


Is it something to do with there being blank rows present? (This was a CSV file which I converted to Numbers.)


User uploaded file

MacBook Pro (15-inch Mid 2012), macOS Sierra (10.12.4), SSD, Optical Drive swapped for HD

Posted on

Reply

Dec 17, 2017 6:02 PM in response to JDfunky In response to JDfunky

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

Dec 17, 2017 6:02 PM

Reply Helpful

Dec 17, 2017 6:12 PM in response to Wayne Contello In response to Wayne Contello

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:

User uploaded file


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

User uploaded file

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

Dec 17, 2017 6:12 PM

Reply Helpful

Dec 18, 2017 3:16 AM in response to Barry In response to Barry

Hi Barry, thanks for your suggestions, definitely some strange stuff going on in this sheet!


Some other things i have observed:

  • When I highlight a text box, down at the bottom left it should display the full text of that box, but it is cut off, e.g. "BTC-STRAT" cell shows as "BTC-ST" and the last letter is cut off.
  • If I put the cursor in to edit a text box and move cursor to the left, the cursor scrolls past one letter, then doesn't move the next time arrow key is pressed, then moves one letter, then doesn't move, and so on through the whole box.
  • If I try a search just for one letter, "B", the results highlight all the letter "T"s instead!! (The letter following B in BTC). Weird.


Okay, so then I tried your suggestions:

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

Yes.

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

Yes, and this is happening on a larger scale, so I then carried out your next instructions.

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.


I wonder what is going on?

Dec 18, 2017 3:16 AM

Reply Helpful

Dec 18, 2017 3:50 PM in response to JDfunky In response to JDfunky

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

Dec 18, 2017 3:50 PM

Reply Helpful

Dec 19, 2017 9:05 PM in response to Barry In response to Barry

Barry wrote:


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.



Bottom left window shows a cut off version of the selected text box. (see screenshot for example)

User uploaded file


Text format is set to black.


Then there's the strange behaviour of scrolling through text where it requires clicking the arrow button twice to get past each letter.. which would indicate there is a space between every character?


My guess is this issue is something to do with it being a CSV file that was imported into numbers. I googled around a bit and there is mention of of a variety of similar problems when csv files are imported, e.g. extra characters etc. But didn't find anything specific that would help me.

Dec 19, 2017 9:05 PM

Reply Helpful

Dec 19, 2017 9:22 PM in response to JDfunky In response to JDfunky

[Hmm tried to edit my post to add further info, but got error message saying: "Your content could not be saved please try again in a few moments". Tried numerous times, no luck so adding a new comment here]


I tried something else to find out if there are some invisible characters hiding in between the letters, something to do with the CSV import. Sure enough, I copied "BTC-STRAT" cell text and then pasted into the Text Wrangler app, and it revealed these weird upside-down red question marks in between the letters:


User uploaded file


They are ASCII characters of some sort?

Dec 19, 2017 9:22 PM

Reply Helpful

Dec 20, 2017 11:17 PM in response to JDfunky In response to JDfunky

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

Dec 20, 2017 11:17 PM

Reply Helpful

Dec 22, 2017 4:19 AM in response to Barry In response to Barry

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

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?)


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.

OK, so here is the text from the BTC-STRAT cell, copied from Numbers then pasted directly here on the next line:



.. that's it, I pasted it in the line above this... it shows nothing at all! The only way I can paste it in any app to get it to show up, is if I paste it into Text Wrangler.


If I then copy from Text Wrangler and paste here, it comes out as:

BTC-STRAT

Dec 22, 2017 4:19 AM

Reply Helpful

Dec 22, 2017 1:12 PM in response to JDfunky In response to JDfunky

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.

User uploaded file

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

Dec 22, 2017 1:12 PM

Reply Helpful
User profile for user: JDfunky

Question: Find & Replace, text not found