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

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

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

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

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

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



.

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

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.

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]

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


.

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.