| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if alpha character | Excel Discussion (Misc queries) | |||
| How to test if the end character of a string is within a user-definedlist? | Excel Discussion (Misc queries) | |||
| Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
| Function to return Character Position of Xth character within a string | Excel Programming | |||
| Increment Alpha Character | Excel Programming | |||
Linear Mode
