Transposing a Single cell value to multiple r

Closed
Report
-
 Trowa -
Hello everyone,
I used rept function (Code used: =rept("Hello,",5)) to get the followin "Hello,Hello,Hello,Hello,Hello," in cell A1.

Now I wanted to transpose that cell value into 5 rows from B1 to B5. Please help me in doing this.

Expected Output:
            1                                                2 
1   Hello,Hello,Hello,Hello,Hello                          Hello
2                                                          Hello
3                                                          Hello
4                                                          Hello
5                                                          Hello


Thanks in advance,
Anand

1 reply

Hi Anand,

First copy cell A1 and paste special into another cell choosing the option "Values".
Now select Data > Text to columns > next > check the komma option > next > finish.
Now select the 5 cells containing "Hello," > copy > paste special > check the transpose option.

That will do the job.

Best regards,
Trowa
Hi Trowa,
I know this process / procedure. In the above, the example that I have given the number of times the word "Hello" will vary, its not a standard of 5 or 10.

Is there any other way where I can do this programmatically?.

Thanks
Anand
> Anand
Have you tried to record the procedure in a macro?
> Trowa
Hi Trowa,
No, I have not tried that and also I'm not quite sure on how to do that.

Thanks
Anand
> Anand
Hi Anand,

To record a macro look at the top menu's and click on Extra > Macro > Record new macro.
Now you get a window where you can put the name of the macro and also a shortcut key, both are optional.
When your done click OK and from now on every move you make is recorded in the macro.
Follow the procedure for separating and transposing your cell.
When you are done go to the top menu's again and click on Extra > Macro > Stop recording.

To run the macro you could use the top menu's again or simply press Alt+F8, select your macro and press OK.

It's also advisable to take a look at the code by pressing Alt+F11 and try to understand what is happening. This way you can make small adjustments (maybe to change which cells are affected) to better suite your needs.

Take some time to experiment and post back if you get stuck somewhere.

Best regards,
Trowa