[VBA] find punctuation in a string

[Christian_Ku]

Hello, I want to search for punctuation in strings. Is this possible or should i do this with a for loop or something like that?

 

[Jon von der Heyden]

Hello

What do you want to do with the punctuation? Simply test if there is any (TRUE / FALSE). Remove it?

 

[Christian_Ku]

Well, actually I want to find the way ActiveCell.DirectPrecedent.Address is seperated, because sometimes this is with a , and sometimes with a :, and I don't know if other punctuations exist for the separation of precedent cells.

 

[northwolves]

It's easy:

MsgBox ActiveCell.DirectPrecedents.Address Like "*[,:]*"

Regards
Northwolves

 

[Christian_Ku]

Ok, I can work that in an IF expression, now, does anyone know if there are other ways excel uses to split those cells. Or are the comma and semicolon the only ones?

Thakns for the replies!

 

[Jon von der Heyden]

Hello

You can use this to test which punctuations are included:

Function GetPunc(str As String)
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\w"
If .test(str) Then _
GetPunc = .Replace((str), "")
End With
End Function

applied as follows:

Sub ShowPunc()
MsgBox GetPunc(ActiveCell.DirectPrecedents.Address(0, 0))
End Sub


I think only , and : are possible. If your precedent references another sheet (e.g. Sheet1!A1) then that refence won't be included hence exclamation mark shouldn't feature.

What do you mean 'split those cells'? Do you mean you want to return each cell noted in the address? E.g. A1:A10 - we can split to show A1 and A10, or are you wanting to show each cell within that range?

If all you want is to return each cell referenced in the string then try this:

Function GetElement(ByVal Text As String, ByVal n As Long, Optional ByVal Delimiter As String = " ")
GetElement = Split(Text & String(n - 1, Delimiter), Delimiter)(n - 1)
End Function


Function PuncCount(str As String)
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
If .test(str) Then _
PuncCount = Len(str) - Len(.Replace((str), ""))
End With
End Function

Sub SplitString()
Dim MyString As String
MyString = ActiveCell.DirectPrecedents.Address(0, 0)
For i = 1 To PuncCount(MyString) + 1
MsgBox GetElement(Replace(MyString, ":", ","), i, ",")
Next i
End Sub

This assumes that the only punctuation / delimiters are comma's and colons.

 

[Christian_Ku]

Thanks a bunch, it will take some time before I understand entirely what you have done here though ! Especially the functions are very useful (not only for the things I currently am working at). To answer your question: I wanted A1:A10 to split in an array of only A1 and A10, so that was a correct assumption.