Find Text without Delimiting

[remeng]

Hi,

I've got data that has 3 sections to it, but I need to determine each section's value. I'd like to do this without delimiting the cell and having to remove columns later.

Situation:

FACE:BACK:XBAND

Face = variable
Back = Variable
XBAND = variable

Variable values:

GPL
NONE
LAM
LAMBCKR
FORMBLK
PSA
RC
GPLBCK
R (any value that starts with "R")

Examples:

GPL:GPL:
GPL:NONE:XBAND
LAMBCKR:NONE:
NONE:NONE:
LAM:LAMBCKR

The problems that I am running into is with regards to first occurrence vs. second occurrence. The code I need to run is check before the first colon and ignore anything after that. Then run code to check between the first and second colon.

I know that I can delimit the values with the ":: as the delimit value into multiple columns and that will work, but is there a way to do this in the background in the VBA without adding columns in the worksheet itself?

I want to avoid concatenation after the evaluation.

Thanks,

Mike

 

[Andrzejek]

Looks like Split() function is your friend:

Data:[pre]
A
1 SAMPLE
2 GPL:GPL:
3 GPL:NONE:XBAND
4 LAMBCKR:NONE:
5 NONE:NONE:
6 LAM:LAMBCKR[/pre]

Option Explicit

Sub remeng()
Dim R As Integer
Dim i As Integer
Dim ary() As String

R = 2
Do While Range("A" & R).Value <> ""
    ary = [blue]Split([/blue]Range("A" & R).Value[blue], ":")[/blue]
    For i = LBound(ary) To UBound(ary)
        Debug.Print ary(i)
    Next i
    R = R + 1
Loop

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Andy,

That was it! Thanks buddy,

Mike