Pasteing across multiple columns

LEJ · General User Joined: 08 Feb 2012 Posts: 7

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

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

I am not quite sure what you are asking but

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

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

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

LEJ · General User Joined: 08 Feb 2012 Posts: 7

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 · Posted: Wed Feb 08, 2012 11:17 pm Post subject:

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

B1

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

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

LEJ · General User Joined: 08 Feb 2012 Posts: 7

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 · 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
Your text

B1

LEJ · General User Joined: 08 Feb 2012 Posts: 7

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

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