OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Imposing Character limitations

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
anthony132
General User
General User


Joined: 01 Jun 2012
Posts: 6

PostPosted: Sat Jun 02, 2012 9:40 am    Post subject: Imposing Character limitations Reply with quote

I'm trying to upload a large Calc file onto a website, but they have a limit of eighty characters for my Title column. Each cell is fill with text. How do I reduce all of them to 80 characters or less in one swoop. I'm very novice to spreadsheets of any kind so forgive me if the terms I'm using aren't correct. I'm trying my best to explain my problem.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jun 02, 2012 5:25 pm    Post subject: Reply with quote

Maybe in a spare column use =LEFT(A1;80) filled down to the last row with a title then use Copy/Paste Special to paste only the Text over the titles that I have assumed start in A1.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
anthony132
General User
General User


Joined: 01 Jun 2012
Posts: 6

PostPosted: Sat Jun 02, 2012 6:33 pm    Post subject: Reply with quote

Sorry, I'm not sure what you told me to do. Are you talking about creating the same text as the text that will populate every cell under the header labled "Title"? Each cell's text is unique to the product it is describing. I need the cells to keep that uniqueness. If I can't shorten the characters in each cell all at once then maybe there is the way to just click on a cell, and have the program tell me how many characters are contained within. If it is over eighty then I need to cut a few words out. Please clarify what you meant or let me know if there is another solution you have. Thanks for your help.
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Sat Jun 02, 2012 6:54 pm    Post subject: Reply with quote

The formula , in an adjacent column, will in essence copy the first 80 characters in
A1 to , say , B1.

=LEFT(A1;80) ; as the formula is copied down in Col. B, the A1 cell reference will
change to cell A2 , then A3 and so on all the way to the end row of Col. A that has
data.

What ever column you put the formula in, make sure its' column width is such that
the 80 characters will fit; otherwise, the row height will adjust higher.
_________________
OO 3.2.1
Win XP /SP3
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jun 02, 2012 9:11 pm    Post subject: Reply with quote

See attached doc (Reduce Titles to 80 characters.ods)...
http://www.mediafire.com/view/?wdrhlv859hx1kuq
On the first sheet A2:A8 have text which may or may not contain more than 80 characters.
On the second sheet I have assumed column G is spare and have entered =LEFT(A2;80) into G2 then dragged the G2 fill handle (little black blob in G2's bottom-right corner when G2 is the only selected cell) down to G8 (scsisys has described what happen there).
These formulae return the first 80 characters of the column A cell on the same row.
On the third sheet I have copied G2:G8 then selected A2 then used Paste Special (Ctrl+Shift+V) to paste only the results of the column G formulae over the top of the original texts.
I have included a picture of the Paste Special dialogue to show that only the "Text" option should be ticked.
On the fourth sheet the final result is shown after the column G formulae have been deleted.

I have used multiple sheets only to illustrate the steps to follow. Of course it should all be done on the one sheet.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Sun Sep 23, 2012 4:00 am; edited 1 time in total
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sun Jun 03, 2012 1:32 am    Post subject: Reply with quote

Hi
Copy your Data to the next sheet and:

→search and replace
    search for:
    ^(.{80}).*$
    replace with:
    $1
    with Option [x]Regular Expression


Karo
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group