shift part of contents of a cell to another cell

[warby1212]

Hi, I have a spreadsheet which I need to modify to import into a database. In some cells there are two values (one above the other). Does anyone perhaps know a trick for taking one of the rows (within the cell) and putting it into an empty column next to it? (it's a big spreadsheet!)

I should write something here.

 

[yogia]

Hi warby1212:

If I understand you correctly, your two values in the cell are separated by CHAR(10). See the following to separate the two values into teo different cells ...

    A       B       C
  -|-------------------
   |23
 1 |       23      16
   |16 
  -|------------------- 
   |31
 2 |       31       7
   |7
  -|-------------------

formula in cell B1 is ... =LEFT($A1,FIND(CHAR(10),$A1)-1)

formula in cell C1 is ... =MID($A1,FIND(CHAR(10),$A1)+1,255)

Would this do?



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hi,

It would help if you copy 'n' paste a sample of your data here.

"In some cells there are two values (one above the other)."

It could be a CHAR(10) or CHAR(13), probably the former. You could use Data>Text to columns.



Skip,

_[red][/red]

 

[warby1212]

That's it! You're both right, it's a line break. I wasn't familiar with how to put it in or get it out. This spreadsheet is a monster that little problem is the one I couldn't get of about 20 steps to prepare it for an import. Thanks very much.

I'm really not in management but I can't change my profile (I'm a programmer)