PDA

View Full Version : Select issue



nedy_03
09-04-2007, 07:01 AM
Hello,

I would need a formula that selects froam a cell a word that begins with "sect" and ha a length of 9 characters.

Something like select "sect****" ... Can anybody help me with taht ?

thx,
Nedy

rory
09-04-2007, 07:04 AM
How about:
=IF(COUNTIF(A1,"*sect?????*"),MID(A1,SEARCH("Sect",A1),9),"")

mvidas
09-04-2007, 07:08 AM
Rory's solution may return spaces; if that is not a problem then use that.

As far as I know (and I hope to be proven wrong) there would be no built-in function to do what you're looking for. You could always create your own function though and use that. Insert a standard module into your workbook, and paste in the following:Function ReturnSectWord(ByVal vInput As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.ignorecase = True
RegEx.Pattern = "\bsect\w{5}\b"
If RegEx.test(vInput) Then
ReturnSectWord = RegEx.Execute(vInput).Item(0)
End If
Set RegEx = Nothing
End FunctionThen just call it like =RETURNSECTWORD(A1)

rory
09-04-2007, 07:12 AM
Matt,
I had a feeling you'd have a RegExp solution for this... :)

mvidas
09-04-2007, 07:28 AM
When it fits... :) I thought about a non-regexp way (using Like and InStr) but couldn't think of a short enough version to make it worthwhile. I could find out if it contained the word easy enough (If LCase(" " & vInput & " ") Like "* sect[a-z_0-9][a-z_0-9][a-z_0-9][a-z_0-9][a-z_0-9] *" Then), but couldn't think of a way to retrieve the word without looping through the string. Regexp works fast anyways, so why not :)

nedy_03
09-04-2007, 08:35 AM
THHHXXXX ...

nedy_03
09-04-2007, 08:46 AM
The 7th character is a "space" .. And I would need that :)

nedy_03
09-04-2007, 08:54 AM
Thx it perfectly works ... I know I have an extra solution if I ever need no spaces :)

mvidas
09-04-2007, 08:55 AM
Ok, if the seventh is always a space, then make the following change:' RegEx.Pattern = "\bsect\w{5}\b"
RegEx.Pattern = "\bsect\w\w \w\w\b"

mvidas
09-04-2007, 08:56 AM
If his formula way works for you, use it :) That way you don't need to use macros in there.
Be warned though if your cell contains "Section 25" or something, it will return "Section 2"