I have a listing of items and one column is now a combination of detail
lines, and product part #'s. I need to be able to seperate the two. The two
types appear in no particular order or sequence that is consistant.

One characteristic of the part #'s is that they always start with at least
two capital letters. Sometimes there are more caps, but at least 2.

Is there a way to move the contents of a cell IF the first two letters of
the cell are caps? Example below.

What I have now:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor

What I want to end up with:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor

Hello Scott,
'----------------
Sub TestFirstTwoCharacters()
Dim rngCell As Excel.Range
Dim lngChar1 As Integer
Dim lngChar2 As Integer
Dim strText As String

For Each rngCell In Selection.Cells
strText = rngCell.Text
If Len(strText) Then
lngChar1 = Asc(strText)
lngChar2 = Asc(Mid$(strText, 2, 1))

If lngChar1 64 And lngChar1 < 91 Then
If lngChar2 64 And lngChar2 < 91 Then
rngCell(1, 2).Value = strText
rngCell.ClearContents
End If
End If
End If
Next 'rngCell
End Sub
'----------------------
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Scott Wagner"
wrote in message...
I have a listing of items and one column is now a combination of detail
lines, and product part #'s. I need to be able to seperate the two. The two
types appear in no particular order or sequence that is consistant.
One characteristic of the part #'s is that they always start with at least
two capital letters. Sometimes there are more caps, but at least 2.
Is there a way to move the contents of a cell IF the first two letters of
the cell are caps? Example below.

What I have now:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor

What I want to end up with:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor

AWESOME!

Thanks Jim!

"Jim Cone" wrote:

Hello Scott,
'----------------
Sub TestFirstTwoCharacters()
Dim rngCell As Excel.Range
Dim lngChar1 As Integer
Dim lngChar2 As Integer
Dim strText As String

For Each rngCell In Selection.Cells
strText = rngCell.Text
If Len(strText) Then
lngChar1 = Asc(strText)
lngChar2 = Asc(Mid$(strText, 2, 1))

If lngChar1 64 And lngChar1 < 91 Then
If lngChar2 64 And lngChar2 < 91 Then
rngCell(1, 2).Value = strText
rngCell.ClearContents
End If
End If
End If
Next 'rngCell
End Sub
'----------------------
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Scott Wagner"
wrote in message...
I have a listing of items and one column is now a combination of detail
lines, and product part #'s. I need to be able to seperate the two. The two
types appear in no particular order or sequence that is consistant.
One characteristic of the part #'s is that they always start with at least
two capital letters. Sometimes there are more caps, but at least 2.
Is there a way to move the contents of a cell IF the first two letters of
the cell are caps? Example below.

What I have now:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor

What I want to end up with:
Col A Col B Col C
1 AB123
1 Outdoor
1 BC456
1 DEF980
1 Indoor