Finding first non-alphabetic character

[Becky]

Hi,

I'm trying to make a macro I have working in Word also work in Excel,
but I'm having problems selecting words the same way. As far as I
know there is no equivalent to what in Word is Selection.EndOfUnit :=
wdWord, Extend := wdExtend

To make do, I've been searching for spaces using
stopPosition = InStr(startPosition, sentence, " ", vbTextCompare)
thisWord = Mid(sentence, startPosition, stopPosition - startPosition)

The problem is in Word non-alphabetic characters such as "[,.:;]" are
also used to mark the end of a word, besides just spaces. So for
example if "hello) " was part of the text in "sentence", in Word only
"hello" would be selected but in Excel by my method the closing
bracket would also be selected "hello)".

I'm looking for a way to find these rare cases when a non-alphabetic
character is attatched to or imbedded in a word.

I was wondering if there is a way to search, similar to InStr, but for
the first of a variety of characters, or the first character to match
some test, like character < Chr(32).

Does anyone know of anyway to achieve this without looping through all
the characters?

Thanks for any suggestions,
Becky

[David McRitchie]

Hi Becky,
Would the following provide what you really want to end up with?

ExtractElement returns "0133", which is the fourth element in the string. The
string uses a hyphen (-) as the separator in the following example:
=ExtractElement("123-456-789-0133-8844",4,"-")

Code for ExtractElement at http://www.j-walk.com/ss/excel/tips/tip32.htm

An alternative is Laurent Longre's WMID (part of his free
MOREFUNC.XLL add-in, available at http://longre.free.fr/english)
=TRIM(WMID(A1,1,1,"~"))

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Becky" <beckyw...@hotmail.com> wrote ...

[Becky]

Thanks for the suggestions David.

I'm actually looking for something that would do something more like
return the position of the first "-", but without knowing whether it's
a "-", or a ":", ";" ...
For example with the string "abc:def-ghi" it would return 4 since the
first non-alphabetic character is in the fourth position.

Thanks again,
Becky

"David McRitchie" <dmcri...@msn.com> wrote in message news:<OuM1ZNZEBHA.1356@tkmsftngp02>...

[David McRitchie]

Hi Becky,

Function Pos_nonalpha(cell) As Integer
Dim i As Integer
For i = 1 To Len(cell)
Dim Number
Select Case Asc(Mid(cell, i, 1))
Case 0 To 64, 91 To 96, 123 To 191
Pos_nonalpha = i
Exit Function
End Select
Next i
Pos_nonalpha = 0
End Function

Most of 192 to 255 are characters in other languages
with the exceptions of 215 and 247, so you can refine
the above to suit your data.

65-90 are uppercase letters, 97-122 are lowercase letters.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Becky" <beckyw...@hotmail.com> wrote in message news:f202fc81.01072...@posting.google.com...