The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text. To reenter all the data as number: 1. Highlight all of the cells and use Format - Cells to change the cell format to an appropriate number format.
Does Mac Excel 2011 include the ability to format areas as Excel Tables? In Windows Excel, this is on the main tab: Format as Table. This does exactly what you need. You can add a total row automatically at the bottom of the table but I find it easier to manually insert a row above the table and then use the same table sub-totals formula. Is there a setting that tells Excel to stop treating blanks as zeros? Replace cells containing zero with blank. Differentiate Between Zero and Blank in Excel. How do I set Google as Safari Homepage on my iPhone? How to delete a function?
With all of the cells still selected, go to the menu Edit - Find & Replace 3. In the Search For box enter. (period asterisk) 4. In the Replace with box enter & 5. Select More Options and check Current Selection Only and Regular Expressions 6. Click Replace All The.
'means zero or more of any character' and & means 'whatever was found'. These are regular expressions which are explained in the help section. Hi.Am receiving '$12345.67 when importing Charles Schwab exported data in csv format into OpenOffice Calc version 4.0.1 AOO401m5(Build:9714) - Rev.
15-09-20 11:40:29 (Fr, 20 Sep 2013) on Windows 8 touch screen operating system. Executed. to find the '$##### in selected rows and used & (ampersand-shift 7) as replacement character.resulting in cell's content being deleted. Any ideas what I could be doing wrong?? Tried to record macro to deleted the ' which did not record easily and therefore resorted to google search leading me to this posting. Any ideas as to what I am doing wrong or how to fix are very much appreciated.
Thanks in advance. Problem solved-another thread described the same problem and gave the solution-setting the 'character set' to Unicode (UTF-8). All is well, and next time I'll do a better search before duplicating someone else's issue. (see additional info at bottom) Disaster!! I seem to have broken NeoOffice completely!!
I'd already found that the find/replace method works to remove apostrophes, but because changing the import settings to US English and checking the special numbers box sounded easier I decided to try it. I must have done something terrible, because now my import window shows a line of Asian characters (to my uneducated eye it looks like Japanese kanji) where the preview of the spreadsheet should be when I try to open a CSV (Excel) file. I've tried changing the settings back but I must be missing something because nothing seems to work.
Here are the current import settings: Character set: Unicode Language: English (USA) Separator options: Tab and Comma Other options: nothing checked Fields: Column type: US English Does anyone know how to fix this? Is it the settings?
Should I uninstall and reinstall NeoOffice? Or is there a NeoOffice preferences file I could delete or reset? Additional info: I'm running NeoOffice 3.4.1 patch 14 on an iMac with OSX 10.9.5. Also, NeoOffice opens a blank window and freezes if I hit 'OK' on the text import page described above, and I have to force-quit the program to get out. FJCC wrote:The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text.
To reenter all the data as number: 1. Highlight all of the cells and use Format - Cells to change the cell format to an appropriate number format. With all of the cells still selected, go to the menu Edit - Find & Replace 3. In the Search For box enter.
(period asterisk) 4. In the Replace with box enter & 5.
Select More Options and check Current Selection Only and Regular Expressions Step 5.5 Select More Options menu, set on 'Search in' the option 'Values' NOT 'formulas' as it is the default 6. Click Replace All The.
'means zero or more of any character' and & means 'whatever was found'. These are regular expressions which are explained in the help section For myopenoffice version, what worked was to SELECT the columns/row of interest AND then to apply, all the other steps!
For some reason, 'converting to numbers' dont work for me! I don't know about importing, but I had the exact same problem with copying and pasting bank records into Open Office spreadsheets. Was maddening as hell until some trial and error. I solved it by copying in the usual manor.
Then selecting (Paste special). Then choose (Unicode) and on the following screen check the (Detect special numbers) box. The numbers were then pasted without the apostrophe in front which treats them as text and won't allow you to use them. This problem just popped up this year 2018-01-07. Never had this problem before that. By, on Flickr.
Fjcc method does not work. I have an excel export from a website with the numbers formatted as text. And I don't ever get an import dialogue, and having spent 2 1/2 hours looking for an import dialoge I give up. Just how in the hell can I get the numbers converted.? Opening the file, it just opens. Setting the load/save options does nothing.
Just where in the hell is this import dialogue? Copy/paste special.nothing. Text to columns is only thing that works. Would be nice to import the damn thing fixed.
Hi Ashish, To move forward, please provide the following information: 1. Does the issue occur if you paste to a blank single cell? Does the issue occur if you choose paste and paste special other options other than text?
Does the issue occur if you paste to a PowerPoint presentation? Your Mac OS version (e.g. 10.12.4) and Excel version. For Mac OS version, from the Apple menu, choose About This Mac. The version number appears beneath “macOS” or “OS X.” For your Excel version, choose Excel in the top menu bar About Excel and please capture a screenshot of the window. Thanks, Tina. Hi Ashish, Thanks for your information.
As Phillip said, please check if these cells are merged cells. If not, to better understand the situation, could you please provide the following information? Copy a sample paragraph in a Word document that you encounter the paste special issue in Excel and paste it here in the forum. A screenshot about “when I do paste special, I only get 2 options: HTML and TEXT”. Detailed steps and related screenshots about “I choose text and then it gets pasted, after that only I can merge the desired cells”. Thanks, Tina.