I have a sheet that has one column with multiple rows. The rows contain numbered content (anywhere from 1-16) on individual lines - this is not a set number, it varies per row. Is there an expression that can be written to take the numbered content and put it in the following cells?

Current layout:

Current Layout

Desired layout:

Desired Layout

I can also export this as a CSV if a Notepad++ expression is necessary.

  • No pre-made function in Excel, but you could try the Text To Columns option (in the Data Ribbon in my Excel version), the problem is for that you need your text to have a delimiter, and your delimiter in this case is the Line Feed which is Chr(10) , and you cannot type that in the box provided, but you could use something like =SUBSTITUTE(A1,CHAR(10),”,”), and then use that as your source with proper comma delimiter so that "Text To Columns" can do its thing. Sorry but that is the easier way I can think of right now. – arana Oct 7 '16 at 21:05
  • 3

Copy cells from Excel to Notepad++, then make the following subs (Ctrl+H, by using regexes, hit Replace All each time):

  1. \n\r to \t
  2. "\t" to \n
  3. " to nothing

Select all text, copy, go back to Excel, select 1st cell of your area, paste.

| improve this answer | |

Exported as a csv, opened in notepad++ then found every instance of a number (exe, 1., 2, etc.) and replaced with "," and then re-uploaded to excel.

| improve this answer | |
  • Less general, and longer than my solution... (just kidding, maybe mine was less clear) – SΛLVΘ Nov 23 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.