splitting cells

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

What I am trying to do is to enter data into A1, after 20
spaces or values I would like it to go to B1, then after
the next 20, go to C1 for another 10 value. Is there a
way to make it do that while you type into the first
cell, instead of clicking into the next cell after
maximum value is reached?

Our warehousing system takes product description like
that, and I would like to be able to copy and paste cells
straight from our excel spreadsheet (used for listing all
new products to be entered)with out having to go into the
cell and copy and paste the first 20 values, then the
next, and so on. If i could just click a cell and copy
and paste that would save time. I could split it into 3
cells and have the people entering into the spreadsheet
only enter the limited values, but so many people use it
that I think it would be to hard to control.

I hope that makes sense, its rather hard to explain.
Please email me with any suggestions, thanks!!!
 
No, I think it can be done using a combination of LEFT and
MID.


What you will need to do is copy the data you receive
(i.e. want to split up) into D1:D100, say.

Then, in cell A1, enter this formula:

=LEFT(D1,20)

In cell B1, enter this formula:

=MID(D1,21,20)

In cell C1, enter this formula:

=MID(D1,41,10)

Copy each of these formula down and your results will be
displayed for you.

Regards,

Gary Thomson.
 
WOW, THAT WORKS PERFECT!
THANKS!!!
-----Original Message-----
No, I think it can be done using a combination of LEFT and
MID.


What you will need to do is copy the data you receive
(i.e. want to split up) into D1:D100, say.

Then, in cell A1, enter this formula:

=LEFT(D1,20)

In cell B1, enter this formula:

=MID(D1,21,20)

In cell C1, enter this formula:

=MID(D1,41,10)

Copy each of these formula down and your results will be
displayed for you.

Regards,

Gary Thomson.



.
Click to expand...
 
After reading other responses, I'm not sure we understand
what you're trying to do. My interpretation:
"I'd like to start entering values in A1. After
entering a value in A1, the cursor skips to A2, and so on.
After entering a value in A20, I want the cursor to skip
to B1, and so on."

If this is the correct interpretation, I think you can
try a couple of things.
1. Allow entries beyond A20, but have B1=A21,
B2=A22...C1=A41, C2=A42... This will show echoed values
in the spaces you want them to be in. When you copy the
completed set (A1:?20), do a Copy and Paste Special/Values
into the target sheet.

or 2. Create macros for each of the column jumps
(MoveToB1, MoveToC1, etc.). The macro would simply put
the cursor into the target cell. Then, in A21 put a
button that activates the MoveToB1 macro, in B21 put a
button that activates the MoveToC1 macro, etc. This
requires a button click, but the cursor will jump to where
you want it to be - just make sure the users understand
this.

Any help?
 
You can do it in place with an event macro:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const N = 20
Dim vTemp As Long
Dim sTemp As String
Dim i As Long

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
sTemp = .Value
For i = 0 To Int(Len(sTemp) / N)
With .Offset(0, i)
.NumberFormat = "@"
.Value = Mid(sTemp, i * N + 1, N)
End With
Next i
Application.EnableEvents = True
End If
End With
End Sub
 
Back
Top