Take contents of a cell and break into separate rows

[Peptide]

Hello,
If A1 has the work TROPICAL, is there a formula that can break apart the letters so they appear as follows: (Note: A1 could have varying lengths of words)

A2 = T
A3 = R
A4 = O
A5 = P
A6 = I
A7 = C
A8 = A
A9 = L

 

[mse330]

Try the below formula

=MID(A$1,ROW()-1,1)

 

[jtakw]

Hi,

Never mind, already solved.

 

[Peptide]

This works great! One thing I did not realize is that if I need to use in a different area on the spreadsheet, the formula wont work.

For example, if the data is in B15 what is the formula for B16, B17 etc?

B15 = TROPICAL
B16 = T
B17 = R
B18 = O
B19 = P
B20 = I
B21 = C
B22 = A
B23 = L

 

[Joe4]

This works great! One thing I did not realize is that if I need to use in a different area on the spreadsheet, the formula wont work.

Sure it will! You just need to make the proper adjustments to reflect that.

Note the original formula:
=MID(A$1,ROW()-1,1)
the part in red is the cell address of the value you want to apply this to
the number in blue is the row number of that same cell (where the data is found)

So, you would just update the formula to this for your latest example:
=MID(B$15,ROW()-15,1)

 

[Peptide]

Thanks Joe4 that works great!
It was the -15 that I could not figure out.

If I want to cut and paste many times in a worksheet, is there a way that the formula can self adjust? Meaning if data was in B15 and I had to use -15, if I cut/paste to say C5, could the -15 auto become -5? If I Cut/Paste to H10, could the -15 auto become -10?

Thanks in advance!

 

[Peter_SSs]

I think any of the formulas using the ROW() function are problematic. If any row(s) are added or removed above the formulas they will return incorrect results and need manual adjustment to correct.
Better to use the ROWS() function instead as below as that will self-adjust if rows are removed or added above.

With your text to split in B15, then use something like this, copied down.

=MID(B$15,ROWS(B$16:B16),1)

Again the red address refers to the text to be split and the blue text refers to the cell that the first formula is placed in. Note the $ in the first B16 but not in the second.

 

[Peptide]

Peter_SSs,
This is perfect to what I just askwed/posted in #6 . Awesome and Thanks!

 

[Peter_SSs]

Peter_SSs,
This is perfect to what I just askwed/posted in #6 . Awesome and Thanks!

I hadn't seen that post as we posted at almost the same time, & I wasn't answering that particular circumstance. Are you sure it works how you want?

 

[Peptide]

Thx Peter

Its about 95%

The other 5% is that I can cut/paste say from B15 to C15 and D15 and it works perfecly. But if I want to cut/paste to C5 or H13 then the formula doesn't automatically adjust and I am not sure how to change it - make sense?

Thanks in advance!