test if a character in a string is alpha
 

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

test if a character in a string is alpha
 

Matilda schreef:

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

Hi,

Try testing the opposite way by looking at it as Numeric or not Numeric
.... suppose x is part of the string...

If Not Isnumeric(x) then
.... it is Alpha (?) ...do something
else
do something else
endif


Greetings,

Baj

test if a character in a string is alpha
 

=LEFT(A2,MAX(IF(ISERROR(1*(MID(A2,ROW(INDIRECT("A1 :A"&LEN(A2))),1))),
ROW(INDIRECT("A1:A"&LEN(A2))),0))-1)

--
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

test if a character in a string is alpha
 

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

test if a character in a string is alpha
 

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

test if a character in a string is alpha
 

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

test if a character in a string is alpha
 

Thanks, Baj, this did work for me!

Bob seems to have anticipated heaps of stuff that can go wrong, so am going
to try his also.

Much, much appreciated,

Matilda

"Matilda" wrote:

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

test if a character in a string is alpha
 

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