Ask Your Question
3

Changing Text to numbers

asked 2012-04-10 19:15:14 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I am new to Libre Office and am trying to import a csv file into a spreadsheet. Once imported, the dollar figures have a ' in front which seems to mean that they are text not numbers. I have tried to change the formating but it does not seem to work.

The help section does not seem to help as it only shows how to change numbers to text, not vice versa.

Can anyone help as I really need to get these columns into number format.

Thanks for any help.

i have a large table with lots of zeros. a lot of formulas did not work on the zeros

for example: one row consists of 52 zeros and one time number 710. as average is gives 710, as a mean it shows 710 and as minimum it gives 710.

it looks like the software does not recognize the zeros as numbers. i checked if the numbers where preceded by a ' and could be interpreted as text. this is not the case however; when i change some zeros into 1's the average, mean and min do not change. they stay 710 i have checked the formulas that calculate the average and median and the range is correct. i have selected the whole table and formatted the cells within the table as numbers i rechecked the cells and now it shows that before all zeros there is a " ' " . i used the find and replace function to search for every " '0 " and replace it with a zero. the problem is that it does not find any cells containing " '0 " while i see them in my screen. dont know what to do anymore...

am i missing something? or is it a bug?

please help

=== Start Editing Would the one who edited the very old question recently, please, clearly mark what he (f/m) changed. Otherwise a modification/enhancements of the answers are uselessly aggravated. (@Lupp }End Editing===

edit retag flag offensive close merge delete

Comments

Infornation needed.
Did you already try the 'View'/'Value Highlighting' tool?
Who made the sheet, what file format was it, and what do you know about the settings?
Did you look into the 'Format Cells...'/Numbers' tab for some of the strange zeros? What did it show as format code?
Would you mind to tell your version of LibO?
The number of used cells in a specific row should not be relevant.

Lupp gravatar imageLupp ( 2016-04-13 19:09:13 +0200 )edit

Cells showing the apostrophe indicating a content acceptable as a number but entered expressly as text, do not contain that apostrophe actually.It is an indicator only temporarily generated for the formula bar of a selected cell or during editing of its content.

Lupp gravatar imageLupp ( 2016-04-13 19:16:50 +0200 )edit

I have tried several approaches and NOTHING gets rid of the ' in front on the $ in every cell that was designated to be a CURRENCY-- now everything is '$123.45 type and NO formulas work- AARRRGGHHH.... program has more bugs than ORKIN

BOB2PLAY gravatar imageBOB2PLAY ( 2019-04-01 03:10:35 +0200 )edit

Sorry. Such a comment isn't a reasonable way to get help concerning an issue.
It's obvious, e.g, that an arbitrary currency value given as a string in one or another currency format, must not be recognized by a LibO set to a default document language having a different default currency or currency formats. Similar issue if a document language or a cells Number>Langauage property is set independent of the respective default.
Using any software successfully requires active thinking now and then. Expressing frustration may be "human" as erring is, but is useless, and may frustrate those who try to help.
What I described in my answer years ago still works for me in V 6.2.2.2 under the mentioned restrictions.

Lupp gravatar imageLupp ( 2019-04-01 13:29:19 +0200 )edit

5 Answers

Sort by » oldest newest most voted
1

answered 2012-04-10 22:44:51 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I think the easy way is select the range in a column or the column (only one column of data) and use Menu/Data/Text to column.

But first in the import you can select for every column the format for import, select the column and in the column type box select what you need.

edit flag offensive delete link more

Comments

Thanks for the help. Worked like a charm.

Lazlo gravatar imageLazlo ( 2012-04-11 00:20:44 +0200 )edit

Thanks "Text to column" worked fine for my cell-content ('7:45) when using ' in the field "Text delimiter".

howil gravatar imagehowil ( 2017-08-03 13:07:45 +0200 )edit
2

answered 2016-04-14 14:46:26 +0200

Lupp gravatar image

updated 2016-04-14 14:55:02 +0200

Setting cells formatted to "do not recognise numbers and formulae" aka 'Text', 'Numbers' format code "@" back to a true 'Numbers' format will not automatically change the type of the content (where applicable). A string coding a number or a formula has to run through a kind of editing process to now be recognised as a number or a formula respectively.
To trigger a new recognition after switching off the "@" mode for all the cells you want to recognise numbers and formulae, you best use the 'F & R' tool:
Call 'Edit' > 'Find and Replace...' (Ctrl+H by default)
View 'Other options'
Set chosen 'Regular expressions' , 'Search in:' Formulae
Put .* into 'Search For:' and & into 'Replace With:'
Call 'Replace All'

Done!
Of course you may restrict the procedure to 'Current selection only', but this should not be necessary as any cell content is just newly input itself. The only difference is the fresh recognition which will neither affect cells with already recognised contents nor cells still left in the "@" mode.
For completeness: For single columns you may use the > 'Data' > 'Text to Columns...' tool with advantages. Be careful with the separator settings and click on the column label of the preview window ('Standard') in advance of running the tool.

Possible problems (as always) locale dependent recognition, mainly calendaric dates.
Giving the details would require to write a special guide.

edit flag offensive delete link more

Comments

that worked! really happy with that!!

corjan gravatar imagecorjan ( 2016-04-15 10:39:19 +0200 )edit

Clicking the checkmark next to the start of an answer you can mark it as correct/acceptes. This may help other users searching for help to select relevant hits.

Lupp gravatar imageLupp ( 2016-04-15 11:47:05 +0200 )edit

thanx, have done that.

corjan gravatar imagecorjan ( 2016-04-22 17:29:36 +0200 )edit
0

answered 2012-04-13 15:27:50 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Another help is to tick the box next to "Detect special numbers (such as dates)" which is seen in the Import Options pop-up window.

edit flag offensive delete link more

Comments

Fantastic, thank you! This solves the problem before it can occur, by assuring CSV values with leading dollar signs are imported into the sheet as numbers -- not strings.

frostyjhammer gravatar imagefrostyjhammer ( 2017-04-05 22:25:28 +0200 )edit
0

answered 2015-08-03 16:09:26 +0200

qed gravatar image

The CT2N plugin might be what you want:

http://extensions.libreoffice.org/ext...

edit flag offensive delete link more
0

answered 2016-11-16 07:20:51 +0200

Highlight the cells you want to remove the ' in front of and do a find and replace. find: .* replace: &

If the output is still text, use the Value() function to convert it to a number.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

Stats

Asked: 2012-04-10 19:15:14 +0200

Seen: 56,334 times

Last updated: Aug 17 '17