×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Find Text without Delimiting

Find Text without Delimiting

Find Text without Delimiting

(OP)
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

RE: Find Text without Delimiting

Looks like Split() function is your friend:

Data:
     A
1  SAMPLE
2 GPL:GPL:
3 GPL:NONE:XBAND
4 LAMBCKR:NONE:
5 NONE:NONE:
6 LAM:LAMBCKR 

CODE

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 = Split(Range("A" & R).Value, ":")
    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

RE: Find Text without Delimiting

(OP)
Andy,

That was it! Thanks buddy,

Mike

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close