Challenge Parsing characters in a String

[VectorZ]

My problem is: I'm trying to parse out certain text patterns from the
first column cell to the second column cell if that cell contains those
patterns, if not nulls the cell. The text string in the first column
cell does not contain any spaces, commas, hashes or normal patterns
that you can lock onto for filtering. Tried building an Array but
having problems with the filtering. My previous attempts to do this
haven't workedand I haven't managed to find work arounds using other
functions.

The patterns I need to parse on are ## - #S, # - #S, RG###, -FO and
several others.
Example: 24-2S, 2-2S, RG142, FO
All the cells in the first column are the ones I need to look at.

Example:
Columns
--------------------------------------------------
Rows |AB2021A24-2S | |
|-------------------------|----------------------|
|AB2022A2-2S | |
|-------------------------|----------------------|
|BC2003ARG142 | |
|-------------------------|----------------------|
|IP1070A22-4S | |
|-------------------------|----------------------|

Thank You,
VectorZ

[paul.robinson@it-tallaght.ie]

Hi
What do you mean by parse? i.e We can see what you are starting with,
but what do you want to finish with??

regards
Paul

[VectorZ]

Sorry I did not state that. It was late and I was tired when I posted.
What I meant was that if the cell in the first row contained that
pattern then the second column next to the first would contain only the
string it found.

Example:
Found in 1st Column Posted in 2nd Column
AB2021A24-2S 24-2S
AB2022A2-2S RG142
IP1070A22-4S 22-4S

I hope that this helps in clarifying this.

Thank you again,
VectorZ

[VectorZ]

Oops. Fixed Example. Still waking up. Sorry.

Example:
Found in 1st Column Posted in 2nd Column
AB2021A24-2S 24-2S
BC2003ARG142 RG142
IP1070A22-4S 22-4S

VectorZ

[GS]

Hi VectorZ,

You could try something like this. (modify to suit)

Sub ParseMyString3()
Dim s1 As String, s2 As String, s3 As String, s4 As String
Dim iPos As Integer
Dim lLastRow As Long, r As Long


s1 = "A##-#S"
s2 = "A#-#S"
s3 = "ARG###"
s4 = "FO"

With Range("A:A")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For r = 1 To lLastRow
If .Cells(r, 1) <> "" Then
iPos = 0
If .Cells(r).Value Like "*" & s1 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
ElseIf .Cells(r).Value Like "*" & s2 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 4)
ElseIf .Cells(r).Value Like "*" & s3 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
ElseIf .Cells(r).Value Like "*" & s4 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 2)
End If
End If
Next r
End With
End Sub

HTH
Regards,
Garry

[VectorZ]

GS Thank you VERY much. That did the trick. The only issue left is that
the prefix of A in the pattern can change form an A through Z in
character(see example of characters). Changed to this. One thing I'll
have to remember is when adding common patterns to add the longer ones
first to filter before going to the shorter common patterns (see
example of patterns).

Example of Characters:
AB2021A24-2S has an A24-2S but the A before the 24-2S can range from an
A to Z. The
BC2003ARG142 can be filtered off of RG and same with FO and many others

Example of Patterns:
s1 = "###-#S"
s2 = "##-#S"
s3 = "#-#S"

Changed Code and works great:

Sub ParseMyString3()
Dim s1 As String, s2 As String, s3 As String, s4 As String
Dim iPos As Integer
Dim lLastRow As Long, r As Long

s1 = "##-#S"
s2 = "#-#S"
s3 = "RG###"
s4 = "FO"

With Range("A:A")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For r = 1 To lLastRow
If .Cells(r, 1) <> "" Then
iPos = 0
If .Cells(r).Value Like "*" & s1 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
ElseIf .Cells(r).Value Like "*" & s2 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 4)
ElseIf .Cells(r).Value Like "*" & s3 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
ElseIf .Cells(r).Value Like "*" & s4 Then
.Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 2)
End If
End If
Next r
End With
End Sub

[GS]

You're welcome!

I wasn't sure about the consistency of the "actual" pattern so I went with
what you posted to demonstrate differentiating between similar patterns. (ie.
s2 is found in s1)Your solution to modify the patterns exact and order them
as you have is a good idea.

Regards,
Garry

[GS]

A correction is needed for s4, it should be s4 = "-FO"

Regards,
Garry



"GS" wrote:

> Hi VectorZ,
>
> You could try something like this. (modify to suit)
>
> Sub ParseMyString3()
> Dim s1 As String, s2 As String, s3 As String, s4 As String
> Dim iPos As Integer
> Dim lLastRow As Long, r As Long
>
>
> s1 = "A##-#S"
> s2 = "A#-#S"
> s3 = "ARG###"
> s4 = "FO"
>
> With Range("A:A")
> lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> For r = 1 To lLastRow
> If .Cells(r, 1) <> "" Then
> iPos = 0
> If .Cells(r).Value Like "*" & s1 Then
> .Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
> ElseIf .Cells(r).Value Like "*" & s2 Then
> .Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 4)
> ElseIf .Cells(r).Value Like "*" & s3 Then
> .Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 5)
> ElseIf .Cells(r).Value Like "*" & s4 Then
> .Cells(r, 2).Value = Right$(.Cells(r, 1).Value, 2)
> End If
> End If
> Next r
> End With
> End Sub
>
> HTH
> Regards,
> Garry