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

Pasteing across multiple columns
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
LEJ
General User
General User


Joined: 08 Feb 2012
Posts: 7

PostPosted: Wed Feb 08, 2012 6:33 pm    Post subject: Pasteing across multiple columns Reply with quote

SOLVED - Thanks to Ozzie - Love your work!!!!

I have a specialised spreadsheet where i need to paste a lot of data.
There are 11 columns each between 74 - 85 characters wide
I have passages of text that can be from 200 to 800 characters long
I want to paste the text so it starts filling the first column then goes to the next column and fills that etc.... until there are no more characters to paste. This needs to be done WITHOUT cutting words in half. For example if the column is 80 characters wide and while pasteing the a full word ends at character 74 and the next word is 10 characters, this word will start in the next column

I hope this explains my requirement
I have tried LEFT[] and MID[] etc... but they break up words - again - I need to keep the words whole

LEJ


Last edited by LEJ on Fri Mar 02, 2012 12:55 am; edited 2 times in total
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 08, 2012 6:39 pm    Post subject: Reply with quote

I am not quite sure what you are asking but
Code:
=CHAR(10)

will add a line break where you want it
_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 08, 2012 7:21 pm    Post subject: Reply with quote

Sorry i mis read your question. perhaps this is more to your needs

Code:
=IF(LEN(A1)>80;LEFT(A1;SEARCH(" ";A1;73));A1)


Am I on the right track at least for the first column?

Edit addition of this line
This formula assumes a word break between character73 and 80
_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 08, 2012 7:49 pm    Post subject: Reply with quote

Refined it a little (your 200 characters are in A1) this is in B1 or whever you want it.

Code:
=IF(ISNUMBER(SEARCH(" ";A1;75));LEFT(A1;SEARCH(" ";A1;75));LEFT(A1;SEARCH(" ";A1;70)))

_________________
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
LEJ
General User
General User


Joined: 08 Feb 2012
Posts: 7

PostPosted: Wed Feb 08, 2012 9:32 pm    Post subject: Reply with quote

Ozzie,

Thank you
This is the closest i have ever got
What you have written works in the first column
What would I have to do for the 2nd and subsequent columns
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 08, 2012 11:17 pm    Post subject: Reply with quote

I've changed B1 again as the last formula wasn't right

B1
Code:
=IF(SEARCH(" ";A1;75)<80;LEFT(A1;SEARCH(" ";A1;75));LEFT(A1;SEARCH(" ";A1;70)))


C1
Code:
=IF(SEARCH(" ";A1;LEN(B1)+77)<LEN(B1)+82;MID(A1;LEN(B1)+1;(SEARCH(" ";A1;LEN(B1)+77)-LEN(B1)));MID(A1;LEN(B1)+1;(SEARCH(" ";A1;LEN(B1)+72)-LEN(B1))))


Check these out and if there ok then the next columns should be easier.
I thought we were only looking at a maximum of 200 characters but i now see 800 Rolling Eyes
_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 08, 2012 11:23 pm    Post subject: Reply with quote

Actually I might change to helper colums as the future columns look messy and large!
_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu Feb 09, 2012 12:30 am    Post subject: Reply with quote

Hi LEJ
Forget everything I've told you, helper columns is the way to go

B1
Code:
=IF(SEARCH(" ";$A$1;75)<80;SEARCH(" ";$A$1;75);SEARCH(" ";$A$1;70))


C1
Code:
=IF(SEARCH(" ";$A$1;B1+76)<B1+81;SEARCH(" ";$A$1;B1+76);SEARCH(" ";$A$1;B1+71))


And now the C1 formula can be dragged across with the fill handle as far as neccessary and we have all cut off points These can sorted with LEFT, MID and RIGHT functions.

With an allowance for the original length which you said may vary from 200 hundred to 800.This will be done with an IFERROR and places the last remaing characters in the cell.

I mention all this because I'm stopping for tea and may not get back to this until tomorrow.
_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu Feb 09, 2012 1:05 am    Post subject: Reply with quote

M1
Code:
=LEFT($A$1;B1)


N1
Code:
=MID($A$1;B1+1;C1-B1)


Still the IF and ISERROR to be done but I'll look in tomorrow to see if perhaps you've finished it. Laughing
Cheers
_________________
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
LEJ
General User
General User


Joined: 08 Feb 2012
Posts: 7

PostPosted: Thu Feb 09, 2012 5:07 am    Post subject: Reply with quote

ozzie,

I bow to your superior knowledge - this is amazing
The solutions posted at 5:17pm worked - obviously nees to be extended to column D,E,F etc...
The helper columns (posted at 6:30pm) provided numbers such as 74, 149
I am not sure how to incoporate 7:15pm posting to get this to work

It may make more sense if you have this to work with - this is what I am trying to do - there is a set of ingredients - I need to paste this into eg. A1 and then have it broken into individual columns (as you already understand)
I have had a look the smallest ingredient listed product is 64 characters (1 column)
The remaining 10 columns need to show as blank

*** The reason I need to go through this is because we have around 1000 ingredient tables - this has to be automatic - we cant do this manually - it would take forever.

Thanks again for your assistance and knowledge

INGREDIENTS: Potatoes, chicken, carrots, beans water, flour, (wheat, soy, maize) beef tallow, starch (wheat), gluten (wheat), salt, thickener (1404, 401, 466, 412, 1420, 1422), vegetable oil, wheat flour, soy protein concentrate, thickener maize (1422), milk solids, guar gum, natural flavour (milk, wheat), maltodextrin, rice flour mineral salt (450, 451), acidity regulator (341, 500, 450), maltodextrin (corn), egg white powder, spices, herbs, chicken flavour, spice extract, yeast extracts, ground spices, spice extracts, colour (160c, 160b, 150c, 100), sodium bicarbonate, hydrolysed corn protein, flavours, vitamin (thiamin), dextrose, flavour enhancer (621, 635), food acid, (262, 330, 270, 363, 334), anticaking agent (561), animal fats, sugar, shortening powder, tapioca starch, onion, garlic, canola oil, emulsifier (322 soy), antioxidant (307)
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu Feb 09, 2012 11:22 am    Post subject: Reply with quote

Goodmorning LEJ
I'm posting what i thought was going to be our completed sheet
No problems, I'm enjoying myself (by the way I'm far from being an expert, I just like the problems, like someone doing a crossword)

A1
Your text

B1
Code:
=IF(SEARCH(" ";$A1;75)<80;SEARCH(" ";$A1;75);SEARCH(" ";$A1;70))


C1 > dragged to L1
Code:
=IF(SEARCH(" ";$A1;B1+76)<B1+81;SEARCH(" ";$A1;B1+76);SEARCH(" ";$A1;B1+71))


M1
Code:
=LEFT($A1;B1)


N1 > dragged to W1
Code:
=IF(ISERROR(C1);RIGHT($A1;LEN($A1)-B1);MID($A1;B1+1;C1-B1))


These can be dragged down the page as far as neccessary!
If you have a look at these I'll look at your latest posting
_________________
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
LEJ
General User
General User


Joined: 08 Feb 2012
Posts: 7

PostPosted: Thu Feb 09, 2012 12:35 pm    Post subject: Reply with quote

ozzie

Thanks again
Looks like that is it
Please dont spend any more time on it - you have done enough - at this stage conside this crossword complete
I will work on it over the next few days - testing

If all ok I will post a complete note

Thanks Again
LEJ
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu Feb 09, 2012 12:39 pm    Post subject: Reply with quote

Cheers
_________________
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
LEJ
General User
General User


Joined: 08 Feb 2012
Posts: 7

PostPosted: Sun Feb 12, 2012 4:41 pm    Post subject: Reply with quote

ozzie,

All seems good - but when the ingredients are only short ie 200 - then several of the last columns are empty, therefore resulting in a

#VALUE!

I need this to be nothing - in other words - it can not say anything - not, 0 not a space it must be totally blank
I was trying to see in the line below - where I would put something like

IF ......... = #VALUE! = ""

=IF(ISNUMBER(SEARCH(" ";A1;75));LEFT(A1;SEARCH(" ";A1;75));LEFT(A1;SEARCH(" ";A1;70)))

What do you think?????

Thanks
LEJ
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Sun Feb 12, 2012 10:32 pm    Post subject: Reply with quote

Hi LEJ
Firstly the formula you show is from my earlier work and is no good, throw it away please.
Secondly my formula to use the RIGHT function relies on the first error to show up in the helper columns as such I'm extending the helper columns by one column and then using the double error to signal blank cells to come into effect.

Using the formula's in my 'completed' post
C1 should now be dragged to M1(instead of L1)
N1 is the old M1
O1 dragged to W1 is now
Code:
=IF(AND(ISERROR(C1);ISERROR(D1));"";IF(LEN(N1)+B1>LEN($A1);"";IF(ISERROR(D1);RIGHT($A1;LEN($A1)-C1);MID($A1;C1+1;D1-C1))))


I've posted my work sheet at
http://www.mediafire.com/?4v44x11kewh64mu
_________________
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
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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