[Solved] Change cells with forced text to numbers

Discuss the spreadsheet application
Post Reply
DonaldFR
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am

[Solved] Change cells with forced text to numbers

Post by DonaldFR »

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].
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: change cells with forced text to numbers

Post by jrkrideau »

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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
DonaldFR
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am

Re: change cells with forced text to numbers

Post by DonaldFR »

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
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: change cells with forced text to numbers

Post by acknak »

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
AOO4/LO5 • Linux • Fedora 23
wope
Posts: 22
Joined: Fri Mar 05, 2010 8:32 pm
Location: Vienna, Austria

Re: change cells with forced text to numbers

Post by wope »

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
vasa1
Volunteer
Posts: 261
Joined: Sat Dec 26, 2009 1:20 pm
Location: Bombay

Re: change cells with forced text to numbers

Post by vasa1 »

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 5.2.1.2 on Lubuntu 16.04 (Openbox)
DonaldFR
Posts: 3
Joined: Mon Mar 08, 2010 2:04 am

Re: change cells with forced text to numbers

Post by DonaldFR »

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
Post Reply