[Solved] Change cells with forced text to numbers

Discuss the spreadsheet application

[Solved] Change cells with forced text to numbers

Postby DonaldFR » Mon Mar 08, 2010 2:09 am

This seems obvious, but I cannot find the answer anywhere. Is there an easy way to globally change the contents of cells that are forced to text by an apostrophe ( 'string ) to numbers without the apostrophe that can be sorted ( string )?

Thanks!

-- Donald
Last edited by Hagar Delest on Mon Mar 08, 2010 1:48 pm, edited 1 time in total.
Reason: tagged [Solved].
DonaldFR
 
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am

Re: change cells with forced text to numbers

Postby jrkrideau » Mon Mar 08, 2010 2:31 am

DonaldFR wrote:This seems obvious, but I cannot find the answer anywhere. Is there an easy way to globally change the contents of cells that are forced to text by an apostrophe ( 'string ) to numbers without the apostrophe that can be sorted ( string )?

Thanks!

-- Donald


The easiest way is to use the VALUE() function to create a new column of numbers.
AOO 4.0.1, Ubuntu 13.04
jrkrideau
 
Posts: 1910
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: change cells with forced text to numbers

Postby DonaldFR » Mon Mar 08, 2010 3:02 am

Thanks, but that doesn't help in my situation (I don't think!).

I've got a colum of British format dates ('DD/MM/YYYY) that I need to change to DD/MM/YYYY in a form that Calc would recognize as a date.

Any ideas?

Thanks!

-- Donald
Open Office 3.1.1. under Ubuntu 9.10
DonaldFR
 
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am

Re: change cells with forced text to numbers

Postby acknak » Mon Mar 08, 2010 3:14 am

1) Select the cells you want to convert
2) Make sure the cells have any number format other than "Text" ("@").
3) Data > Text to Columns
3a) Separated by: Tab (any character not present in the column datsa is OK)
3b) Select the column in the preview and change the column type to Date: D/M/Y
4) Click OK
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: change cells with forced text to numbers

Postby wope » Mon Mar 08, 2010 3:19 am

Hi Donald

=DATEVALUE(A1) and then you must format the cell to a dateformat.

wolfgang
OOo 3.1.1, 3.2.0 SuSE Linux 11.2, Win XPSP2, Win 7
wope
 
Posts: 22
Joined: Fri Mar 05, 2010 8:32 pm
Location: Vienna, Austria

Re: change cells with forced text to numbers

Postby vasa1 » Mon Mar 08, 2010 5:53 am

Another possible route is here:

To get rid of ' before a number .... (seen when some Excel files are opened in Open Office):
Alt,E,F and in search type ".+" and replace with "&" and tick regular expressions.
LibreOffice 3.5.3.2 on Ubuntu 12.04
vasa1
 
Posts: 254
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: change cells with forced text to numbers

Postby DonaldFR » Mon Mar 08, 2010 6:31 am

I tried most of these options, and the one by acknak worked. Thank you all!

-- Donald
Open Office 3.1.1. under Ubuntu 9.10
DonaldFR
 
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests