FJCC wrote:The apostrophe isn't really part of the cell content, it just signifies that the cell is formatted as text.
DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
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.
2. 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
AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution.
Villeroy wrote:AlwaysPaws wrote:To FJCC - it's five years later and I needed this today! Thanks very much for the solution.
You could also use the correct import options to avoid the whole problem.
Villeroy wrote:DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Do not ignore the import options.
Import with US English language and check "special numbers".
robleyd wrote:Villeroy wrote:DeniseKaplan wrote:Any ideas as to what I am doing wrong or how to fix are very much appreciated...
Do not ignore the import options.
Import with US English language and check "special numbers".
Villeroy provided the answer above when he said about special numbers
Rod Henrickson wrote:I don't know about importing,
Murti wrote:Downloaded a CSV file which opened in Open Office 4.1.3 on a Mac OS 10.10.5. It wasn't imported so I didn't have any options on how it opened. Columns showing currency figures wouldn't add up. Looked on here and found a solution to Find and Replace .* with &. It worked for some but this didn't work for me, (maybe a Mac thing?). I'm not a computer expert at all but just thought I'd try Find and Replace a £ for a £. It worked! Obviously the columns have to be displaying the £ or it wont work but that part can be done with the formatting.
It was a fluke, but I thought I would share it.
The one and only answer to this whole f***ng topic is always the same: Do not ignore import options and complain afterwards about the complexity to fix wrongly imported data!
Is there any way to do it through coding?
npandit wrote:IS there any code to avoid apostrophe before numbers?
Cant Format the cells always
Is there any way to do it through coding?
Users browsing this forum: No registered users and 21 guests