# Data into multiple cells and rows

#### consth21

##### New Member
I was wondering how I would go about putting a cell's output into multiple cells and rows. For example, I would like to do a repeat function in a cell, depending on numbers in a previous sheet.
=REPT("thickness, ",A1-1)&TEXT("thickness",1)
A1 ranges from 5-56. In all cases, the output of this cell is greater than 5 columns wide. I can only have an output that is 5 columns and the remaining needs to go into rows. How would I be able to go about doing this? I would like output to be something like this....
thickness, thickness, thickness, thickness, thickness,
thickness, thickness

Heidi

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Just wondering if someone might be able to help me.....PLEASE!!!???!!!

Any help would be greatly appreciated.
Have a great day/night and thank you in advance!

Hi,

Porbably not quite what you want to hear, but couldn't you just merge the 5 columns with say 5 rows and set the wrap text checkbox to true and align to top left?

Ron

Heidi,

Could this give you a start?

Considering that the text to distribute is in cell A1, that we're using 5 columns and that the textseparator is the space.
You change all those items within the macro.

kind regards,
Erik

Code:
``````Sub distribute_words_to_cells()
'Erik Van Geit
'050202

Dim text As String
Dim words(99) As Variant
Dim i As Integer
Dim sp As Integer 'space or other characters
Dim nrcol As Integer

text = Range("A1") & " "
i = 0
nrcol = 5 'to how many columns will the data be ditributed

Do
i = i + 1
sp = InStr(text, " ")
words(i) = Left(text, sp - 1)
text = Right(text, Len(text) - sp)
Loop While text <> ""

For i = 1 To i
Cells(Int((i - 1) / nrcol) + 1, (i - 1) Mod nrcol + 1) = words(i)
Next i

End Sub``````

Well, this sounds exactly like what I need, however now I can't even get the macro to work. Because I have never done this, maybe I am doing it incorrectly. I pasted what you wrote into visual basic and then went back to my cell where the "long" output was. I ran the macro, however nothing happened. Could you help me with this? Also, is there any way that I can add another character and some spaces to the beginning of each row, say a "& (and then the text)?
Thank you so much for your help.
Heiid

Heidi,

Let's first get it to work and then resolve the other questions (remind me when I would forget)

Nothing happened?
What was in your cell A1? Are there single spaces between the words?
Step through the macro with F8, go with your mouse on the different variables (i - sp - words(i) - text) and check the values...

Put in your cell A1 "a b c d e f": it should end up with
A1 : a, B1 : b, ...

can you manage something yet?

kind regards,
Erik

Actually that doens't work either. I am putting this ="a b c d e f" into cell A1. I am then going to tools/macros/run macros and nothing happens. I have tried this both in a different cell and the same cell, but no luck. Any thoughts?
Thank you!

I'm quite sure you're not running the macro

Put this at the beginning of your macro
msgbox "distribute_words_to_cells"
when you run the macro a message should popup

or (and) this
Range("A1") = "a b c d e f g h"
when you run the macro at least cell A1 must be filled with the data

Did you step through your macro with F8 ?

When you goto menu Tools / Macro / Security
what do you see then (what option is checked?)

which version of Excel are you running?

kind regards,
Erik

Hello Erik,
So, I tried adding the message box and that worked, I got one. However when I put the range information in, nothing different happened. Right now, on a seperate worksheet, I have ="a b c d e f g h" in A1. I did step through the macro with F8, however I wasn't quite sure what I was looking at. I am new to programming and wasn't sure what it all meant. I told you I didn't know anything Anyway, I am using office xp. When I checked the security, it was set to medium, which seems like the problem doesn't arise there. Any other input for the beginner?

Thank you again,
Worse case, if it takes me forever to learn this, atleast I'm learning several other things in the process.
Heidi

Heidi,

OK, let's start again.
Office XP no problem of course, security settings OK.

paste this code and run it
Code:
``````Sub distribute_words_to_cells()
'Erik Van Geit
'050202

Dim text As String
Dim words(99) As Variant
Dim i As Integer
Dim sp As Integer 'space or other characters
Dim nrcol As Integer
Range("A1") = "a b c d e f g h" '''''added line for testing

text = Range("A1") & " "
i = 0
nrcol = 5 'to how many columns will the data be ditributed

Do
i = i + 1
sp = InStr(text, " ")
words(i) = Left(text, sp - 1)
text = Right(text, Len(text) - sp)
Loop While text<> ""

For i = 1 To i
Cells(Int((i - 1) / nrcol) + 1, (i - 1) Mod nrcol + 1) = words(i)
Next i

End Sub``````

what do you get ?
I get this
Map1
ABCDE
1abcde
2fgh

"stepping through the code with F8": I should have mentioned too to put aside the VBE-Window so you can see what happens on your sheet

first you'll see cell A1 get "a b c d e f g"
then you'll see nothing for a while since the code is calculating the "words"
(Do .... Loop While text<> "")
starting from For i = 1 To i
(here i has already a value 8, it will start again on 1 going to 8)
your code will loop for all words and put them in the cells
isn't it?

kind regards,
Erik

Replies
0
Views
235
Replies
7
Views
303
Replies
12
Views
294
Replies
3
Views
1K
Replies
10
Views
1K

1,196,134
Messages
6,013,641
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back