Excel Formulas

[Neozero]

I am trying to get an excel formula to break a cell if it is more than 20 characters long and move the remaining characters into the next cell. Anyone have any suggestions ?


Thanks
Jason

 

[bigpapa]

The answers may differ depending on why you are trying to do this? And in particular, what type of data are you working with? I would expect from the description that it is text, but if it is (big) numbers, the answer changes dramatically. Can you give more info?

 

[Neozero]

TO: Bigpapa....

This is text and what I am trying to do is if cell A is more than 20 text characters, I want to leave those 20 text characters in cell A and move the remaining into cell B.

 

[repete]

assuming your data is in column A (and row 3 as an example)
insert the formula =+LEFT(A3,20) in column B; and
insert the formula =+MID(A3,21,400) in column C

splits as required, but doesn't give answer in column A as you indicated.

also assumes tha the string in column A is only 400 characters long

hope this helps

 

[bigpapa]

If you really want to break the text into pieces, using LEFT and MID (as repete suggests) will work, but gets a bit clumsy to manage if you want to split it in more than 2 pieces - that is, for a very long string, split into 20 per cell until it's all used up???

Is splitting it into separate cells really what you want, or would turning on wrapping so that it displays on multiple lines in the same cell serve the purpose?

 

[repete]

that's another way for neozero to consider bigpapa, but be careful to use a non proportional font (eg Courier, NOT Arial etc) otherwise a different number of characters will be on each line depending on the charaters in the source string.

 

[Dreamboat]

Neo, I'd love to know WHY you need to do this?
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com