Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
Hello.
I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
Hi Mike
I believe you will need a VBA function of some sort for this. Before I suggest one, will these cases ever appear: Lowercase letters: 0500531-1a Alpha characters not gathered: 0500531-1A7B Trailing numbers: 0500531-1A45 ? Best wishes Harald "Mike Archer" skrev i melding ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
Mike,
Here is a function Function LastLetters(PartNumber As String) Dim i As Long Dim sLetters As String sLetters = "" For i = Len(PartNumber) To 1 Step -1 If Asc(UCase(Mid(PartNumber, i, 1))) 64 And _ Asc(UCase(Mid(PartNumber, i, 1))) < 92 Then Else If i < Len(PartNumber) Then sLetters = Mid(PartNumber, i + 1, 255) End If Exit For End If Next i LastLetters = sLetters End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike Archer" wrote in message ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
If all part have only a single hyphen, and end in the hyphen followed by
a single digit and then one or more letters, the following will work: =RIGHT(A9,LEN(A9)-FIND("-",A9)-1) If not, then more information is needed about the data. Alan Beban Harald Staff wrote: Hi Mike I believe you will need a VBA function of some sort for this. Before I suggest one, will these cases ever appear: Lowercase letters: 0500531-1a Alpha characters not gathered: 0500531-1A7B Trailing numbers: 0500531-1A45 ? Best wishes Harald "Mike Archer" skrev i melding ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
"Alan Beban" skrev i melding
... If all part have only a single hyphen, and end in the hyphen followed by a single digit DOH ! Didn't give that a single thought. Thanks Alan. Best wishes Harald |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
There will always be a single hyphen followed by up to 3
numeric and 1 or 2 upper case alphas (in that order). -----Original Message----- If all part have only a single hyphen, and end in the hyphen followed by a single digit and then one or more letters, the following will work: =RIGHT(A9,LEN(A9)-FIND("-",A9)-1) If not, then more information is needed about the data. Alan Beban Harald Staff wrote: Hi Mike I believe you will need a VBA function of some sort for this. Before I suggest one, will these cases ever appear: Lowercase letters: 0500531-1a Alpha characters not gathered: 0500531-1A7B Trailing numbers: 0500531-1A45 ? Best wishes Harald "Mike Archer" skrev i melding ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
Just one idea:
Function EndingLetters(S As String) As String Dim p As Long p = Len(S) Do While Mid$(S, p, 1) Like "[A-Za-z]" And p 0 p = p - 1 Loop EndingLetters = Mid$(S, p + 1) End Function Dana DeLouis "Mike Archer" wrote in message ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
" wrote...
There will always be a single hyphen followed by up to 3 numeric and 1 or 2 upper case alphas (in that order). ... If there's always at least one letter at the end of the part number, try =RIGHT(PartNo,1+ISNUMBER(FIND(MID(PartNo,LEN(PartN o)-1,1),"0123456789"))) -- To top-post is human, to bottom-post and snip is sublime. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
Hi Harlan,
Harlan Grove wrote: If there's always at least one letter at the end of the part number, try =RIGHT(PartNo,1+ISNUMBER(FIND(MID(PartNo,LEN(PartN o)-1,1),"0123456789"))) Your formula gives the opposite of what the OP was looking for. I think you meant to write this: =RIGHT(PartNo,2-ISNUMBER(FIND(MID(PartNo,LEN(PartNo)-1,1),"0123456789"))) Another option is: =RIGHT(PartNo,1+ISERROR(VALUE(MID(PartNo,LEN(PartN o)-1,1)))) -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
That worked great. Thank you.
-----Original Message----- Just one idea: Function EndingLetters(S As String) As String Dim p As Long p = Len(S) Do While Mid$(S, p, 1) Like "[A-Za-z]" And p 0 p = p - 1 Loop EndingLetters = Mid$(S, p + 1) End Function Dana DeLouis "Mike Archer" wrote in message ... Hello. I need to set a variable to the be all of the ending alpha characters in a string. The following will work for part numbers ending in 1 alpha character: MyVar = Mid(PartNumber, Len(PartNumber), 1) If PartNumber = "0500531-1A", I need MyVar to be "A" (easy). But if PartNumber = "0500531-1AB", I need MyVar to be "AB". Does InStr have the ability to look for alpha, or will I need a subroutine? TIA, Mike . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid
"Jake Marx" wrote...
... Your formula gives the opposite of what the OP was looking for. I think you meant to write this: =RIGHT(PartNo,2-ISNUMBER(FIND(MID(PartNo,LEN(PartNo)-1,1),"0123456789"))) Another option is: =RIGHT(PartNo,1+ISERROR(VALUE(MID(PartNo,LEN(Part No)-1,1)))) Had meant the latter. Definitely a mental lapse on my part. Appologies to OP. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|