Talk about spoilt for choice! Thanks Alan, it does work a treat.

Happily tripping off to the next stumbling block now.....

Thanks Guys,

Matilda

"Alan" wrote:

Is this what you want?

Sub TestString()

Dim sShift As String
Dim TstStr As String

sShift = <<Populate this variable from somewhere

TstStr = Left(sShift, 2)

If IsNumeric(Left(TstStr, 1)) Then
If Not IsNumeric(Right(TstStr, 1)) Then
TstStr = Left(TstStr, 1)
End If
Else
' Disregard the data
TstStr = ""
End If

End Sub


Matilda wrote:
Hi Bob,

Thankyou... and I should have said I am in VBA not spreadsheet formulas! I
have no chance of working out what your statement is doing, it looks as
though it is errortrapping, testing the type, and putting on the tea!!! I
feel so far away from mastering this stuff ... so glad of your help.

Can you convert it to a VBA statement, please? My variable is sShift and is
a string. I want to get first two characters (which are usually numbers for
workdays) to help me order the list. Where the second character is alpha,
I want only the first, and where the first is an alpha, I want to disregard.

Many thanks,

Matilda

Matilday

"Bob Phillips" wrote:

should have said that it is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Matilda" wrote in message
...
Dear All,

I need to convert part of a string to a number. the string can be x
characters long, but I only want the first teo characters, where they are
numbers.

I am having trouble catching cases where wither first or second character
is
alpha.

If (Mid(sshift, 2, 1) = "A") Or (Mid(sshift, 2, 1) = "P") Then
iShftStart = CInt(Left(sshift, 1)) ' case where shift is written as 8AM
or
8PM
Else
iShftStart = CInt(Left(sshift, 2))
End If

my conditions need to include any ascii alpha character, really.

Any suggestions?


Many thanks,

Matilda