Search code examples
exceloffice-2007

how to move cluttered data from one cell of Excel and distribute to respective cells?

Is it possible to move the data from one row to the another following row in excel sheet?

Image

I want to sort the data in that following excel sheet only and move the data after the marking to the following cell.

Is there any way or technique or a tool to do so? Please help.

Solution

  • Without seeing a pattern that we can use mid() or some such combination to extract, my suggestion is to use "Text to Columns":

    1) Select the range of data that you want to convert.

    2) On the Data tab, in the Data Tools group, click Text to Columns.

    3) In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.

    4) In Step 2, select the Space check box, and then clear the other check boxes under Delimiters.

    The Data preview box shows the first and last names in two separate columns:

    5) Click Next. ... etc (see Link for more).

    You could use a space delimiter, which should get you pretty far. Of course, if there's some pattern that all your cells will follow, to where we know which spaces to separate at, then please let us know and it can be more tailor-made.