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
How to make multiple selections in photoshop - Guide
Allow multiple downloads chrome - Guide
How to delete multiple files on mac - Guide
If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
How to download multiple files from whatsapp web - WhatsApp 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

Fill adjacent cell with number from text answer

Excel function question

Can’t Log into Instagram from Multiple Devices

Return Seq Values Until Blank value then return different set of seq values

Similar discussions

Subscribe To Our Newsletter!