I would like to split data in my rows from looking like this:
| Start Letter No. | End Letter No. | Letter Sequence | Delta D |
| 30 | 33 | ABCD | 0.065 |
to looking like this:
| Start Letter No. | End Letter No. | Letter Sequence | Delta D |
| 30 | 30 | A | 0 |
| 31 | 31 | B | 0.065 |
| 32 | 32 | C | 0.065 |
| 33 | 33 | D | 0.065 |
Where the sequence of letters in the text are split into a single letter in each row and this is associated with it's corresponding number in the sequence. The 'Delta D' should be 0 for the first letter in every sequence but the same number as the original row for all the other letters. I would really like a way to do this without coding so that I can do it/understand it manually.
Thanks!
If you don't want to manually fix a lot of things, I think you will at minimum need some formulas. Below is one possible option:
Create delimiters with new formula column
Concat Items(Words(:Letter Sequence, ""), " ")
Then use text to columns with the new delimiter character
Stack the data table with the new columns
Create new formula column to get the correct Start Letter numbers
Col Min(:Start Letter No., :Letter Sequence) + Col Cumulative Sum(1, :Letter Sequence)
Copy that just created column to other column to get End Letter No (or use formula)
Then you need one more formula, to change first letter's Delta D to 0
If(Row() == Col Min(Row(), :Letter Sequence),
0,
:Delta D
)
Finally cleanup column names to match what you want
