Move cell contents to another column IF

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" <[email protected]>
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" <[email protected]>
> 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
>