Search : in
By :

Transposing a Single cell value to multiple r

Last answer on Oct 22, 2009 12:57:04 pm BST Anand, on Oct 15, 2009 10:06:02 am BST 
 Report this message to moderators

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
Configuration: Microsoft Office/Windows XP/ Internet Explorer 6.0

Best answers for « Transposing a Single cell value to multiple r » in :
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Excel - Send value of cell to target ShowExcel - Send value of cell to target Issue Solution Note Issue Is it possible to send the value of a cell (copy and paste special) to another cell where the value would not be disturbed when the source value changes to '0'? My...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Data transmission - Multiplexing ShowIntroduction to multiplexing Multiplexing refers to the ability to transmit data coming from several pairs of equipment (transmitters and receivers) called low-speed channels on a single physical medium (called the high-speed channel). A...
Ask your question Reply

1

Trowa, on Oct 15, 2009 1:07:29 pm BST

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

   
Reply to Trowa

2

Anand, on Oct 20, 2009 3:40:52 am BST

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

   
Reply to Anand

3

Trowa, on Oct 20, 2009 1:20:00 pm BST

Have you tried to record the procedure in a macro?

   
Reply to Trowa

4

Anand, on Oct 20, 2009 3:15:59 pm BST

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

Thanks
Anand

   
Reply to Anand

5

 Trowa, on Oct 22, 2009 12:57:04 pm BST

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

   
Reply to Trowa
Ask your question Reply
They need your help