Delete all rows that don't start with CRN or AT

[mdmackillop]

You just have to contribute answers to a few thousand questions and you pick up a bit of knowledge along the way! ...or you could buy a book.

[rrtts]

Never mind...figured it out.

[rrtts]

@MD - I ran into a bit of a snag I'm hoping you can help me with...

Can you explain your "Split Text" code - the problem I am having is that if there are multiple words then it deletes some of the text...

For example,

If the text is:

RHHIDEF/UNIT 2 (all in one cell), the result is RHHIDEF UNIT 2 (all in different cells - which is perfect).

But if the text is

RHHIDEF/UNIT 2 A B C (all in one cell), the result is RHHIDEF UNIT 2 (all in different cells - but A B C is deleted - which is bad).

Any ideas...thanks.

(Do you have any recommendations on Excel Books - I'm hesitant to just get the Excel 2003 for Dummies - but it might best fit my level of knowledge...heh heh).

[mdmackillop]

Split creates an array of items as defined by the delimiter so
Arr = Split(RHHIDEF/UNIT 2 A B C,"/") will form a 2 item array
Arr(0) = RHHIDEF
Arr(1) = UNIT 2 A B C
The second bit of the code will split UNIT 2 A B C into 5 items using Space as a delimiter. If you want the A B C in a separate cell. you would need to concatenate these array items with spaces to reform the string.

You need to be sure of your data make up before you can write code to handle it.

[Ken Puls]

...or you could buy a book.

If only it were that easy.

[rrtts]

@MD...great!

Your original code was:

[vba]
Sub SplitText()
Dim cel As Range
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
arr = Split(cel, "/")
If InStr(1, cel, "/") = 0 Then Goto Skipped
cel = arr(0)
cel.Offset(, 1) = Split(arr(1), " ")(0)
cel.Offset(, 2) = Split(arr(1), " ")(1)
Skipped:
Next
End Sub
[/vba]

After tinkering with it and your tips...I realized that once I deleted the 2nd cel.Offset I achieved the desired results.

It's amazing how simple this all seems once someone shows it to you...I feel dumb asking some of the questions I do only to realize a simple tweak gets the job done.

I do appreciate all the help...again.

@Ken Puls - Amen. I go to the local book store and try and read thru various books (like I know what I'm doing heh heh)...there are SO many books on the subject I need a book just to explain all the other books.