Transposing a Single cell value to multiple r

Closed

Anand - Oct 15, 2009 at 05:06 AM
Trowa - Oct 22, 2009 at 07:57 AM

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

R multiple values in one cell
Which key is used to make multiple line in a single cell - Guide
Allow multiple downloads chrome - Guide
How to delete multiple files at once - Guide
How to make multiple selections in photoshop - Guide
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum

1 response

Answer 1 / 1

Trowa
Oct 15, 2009 at 08:07 AM

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

Anand
Oct 19, 2009 at 10:40 PM

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

Trowa > Anand
Oct 20, 2009 at 08:20 AM

Have you tried to record the procedure in a macro?

Anand > Trowa
Oct 20, 2009 at 10:15 AM

Hi Trowa,
No, I have not tried that and also I'm not quite sure on how to do that.

Thanks
Anand

Trowa > Anand
Oct 22, 2009 at 07:57 AM

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

Similar discussions

help me to add particular cells

Excel - IF function with multiple words

Counting cells containing only numbers

If a cell contains any text then add a value

Excel multiple functions in one cell

Similar discussions

Subscribe To Our Newsletter!