split column data

[Katerinia]

Column C has multiple codes
I need to seperate the codes that start with the number 4
from the codes that start with the number 3
and past codes starting with number 4, into empty column D in the same
position they were in Column C

Thanks!

 

[Marcelo]

=IF(LEFT(C6;1)="4";C6;"")
hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Katerinia" escreveu:

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Katerinia]

C D
2 422366
3 30000285
4 409997
5 431094
6 30000265
7 431167
8 30000326
9 465561
10 469002
431170
30000157

I need to move all codes that begin with the number 4 over one column in
place.
C D
1 422366
2 30000285
3 409997
4 431094
5 30000265
6 431167
7 30000326
8 465561
9 469002
10 431170
30000157

 

[Dave Peterson]

I would insert two new columns--one for the 4's and one for the 3's (actually,
the non-4's):

in D1:
=if(left(c1,1)="3",c1,"")

=E1:
=if(d1="",c1,"")

Select D1:E1 and drag down as far as you need.

Then with D:E selected, copy|Paste special|Values.

Then delete (or hide) the original column.

 

[Don Guillett]

Option Explicit
Sub moveoveronecolumn()
Dim lr As Long
Dim c As Range
lr = Cells(Rows.Count, "c").End(xlUp).Row
For Each c In Range("c2:c" & lr)
If Left(c, 1) = "4" Then c.Cut c.Offset(, 1)
Next
End Sub

 

[Katerinia]

Worked beautifully Dave,
8,595 records of data took me 2 minutes instead of the full day I was
originally looking at. Thanks!

 

[Don Guillett]

Seems like a lot of work that the macro could have done for you.