check first 3 Letters of a string !!!

[G21]

HI ,
Need some help !!!
im having a list of data & it contains various patterns. i want to categorize these data depending on first 3 letters of the string.
eg -

ABC1
ABC2
XYZ1
XYZ2

so i want to copy data which contains "ABC" as first 3 letters to sheet1 & copy data contains "XYZ" to sheet2.

RGDS,
G

 

[tusharm]

You can use the LEFT function in Excel or the Left function in VBA to extract the 1st N characters of a string. Then, you can use that information as you see fit.

 

[Richard Schollar]

Hi

Manually you could use Autofilter to do this - use a text filter of "Begins with" and use ABC or XYZ etc and copy from the original sheet to sheet1 and sheet2.

If you want to automate this then most of the code can be had from recording a macro whilst following the above steps.

 

[Aladin Akyurek]

HI ,
Need some help !!!
im having a list of data & it contains various patterns. i want to categorize these data depending on first 3 letters of the string.
eg -

ABC1
ABC2
XYZ1
XYZ2

so i want to copy data which contains "ABC" as first 3 letters to sheet1 & copy data contains "XYZ" to sheet2.

RGDS,
G

Some options:

1] Running Advanced Filter with criteria.

2] Running a formula system...

Assumption: The data is in A2:A5 on the Data sheet.

Sheet1

A1: ABC

A2, just enter:

=COUNTIF(Data!A2:A5,A1&"*")

A3: List

A4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)<=$A$2,INDEX(Data!$A$2:$A$5,
    SMALL(IF(LEFT(Data!$A$2:$A5,3)=$A$1,
     ROW(Data$A$2:$A$5)-ROW(Data$A$2)+1),
      ROWS($A$4:A4))),"")

Repeat a similar process for Sheet2, etc.

 

[pedie]

Try this code. Works for me.

You data is in Col A of your activesheet.

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub TRY()[/FONT]
[FONT=Courier New]Dim c As Range, lr As Long[/FONT]
[FONT=Courier New]lr = Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]For Each c In Range("A1:A" & lr)[/FONT]
[FONT=Courier New] If Left(c.Value, 3) = "ABC" Then[/FONT]
[FONT=Courier New]  c.Copy Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[/FONT]
[FONT=Courier New] ElseIf Left(c.Value, 3) = "XYZ" Then[/FONT]
[FONT=Courier New]  c.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[/FONT]
[FONT=Courier New] Else[/FONT]
[FONT=Courier New] 'Nothing[/FONT]
[FONT=Courier New] End If[/FONT]
[FONT=Courier New]Next c[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[G21]

HI All,
My requirement is done....

Dim cat As String
Dim val As String
cat = ActiveCell.Offset(0, -1).Value
val = Left(cat, 3)

If val = "xxx" Then

etc...


Thanks for the support of everyone who replied. I got some points from all of your replies.

RGDS,
G