[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 Pasteing across multiple columns Goto page 1, 2  Next
Author Message
LEJ
General User

Joined: 08 Feb 2012
Posts: 7

 Posted: Wed Feb 08, 2012 6:33 pm    Post subject: Pasteing across multiple columns 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 LEJLast edited by LEJ on Fri Mar 02, 2012 12:55 am; edited 2 times in total
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Wed Feb 08, 2012 6:39 pm    Post subject:

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

will add a line break where you want it
_________________
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Wed Feb 08, 2012 7:21 pm    Post subject:

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

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

This formula assumes a word break between character73 and 80
_________________
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Wed Feb 08, 2012 7:49 pm    Post subject:

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)))

_________________
LEJ
General User

Joined: 08 Feb 2012
Posts: 7

 Posted: Wed Feb 08, 2012 9:32 pm    Post subject: 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
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Wed Feb 08, 2012 11:17 pm    Post subject:

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)

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
_________________
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

 Posted: Wed Feb 08, 2012 11:23 pm    Post subject: 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).
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Thu Feb 09, 2012 12:30 am    Post subject:

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)

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.
_________________
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Thu Feb 09, 2012 1:05 am    Post subject:

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

Joined: 08 Feb 2012
Posts: 7

 Posted: Thu Feb 09, 2012 5:07 am    Post subject: 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)
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Thu Feb 09, 2012 11:22 am    Post subject:

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

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

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

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

Joined: 08 Feb 2012
Posts: 7

 Posted: Thu Feb 09, 2012 12:35 pm    Post subject: 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
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

 Posted: Thu Feb 09, 2012 12:39 pm    Post subject: Cheers_________________If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
LEJ
General User

Joined: 08 Feb 2012
Posts: 7

 Posted: Sun Feb 12, 2012 4:41 pm    Post subject: 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
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Sun Feb 12, 2012 10:32 pm    Post subject:

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
_________________