Mid

M

Mike Archer

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
 
H

Harald Staff

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
 
B

Bob Phillips

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

Alan Beban

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
 
H

Harald Staff

Alan Beban said:
If all part have only a single hyphen, and end in the hyphen followed by
a single digit
Click to expand...

DOH ! Didn't give that a single thought. Thanks Alan.

Best wishes Harald
 
G

Guest

There will always be a single hyphen followed by up to 3
numeric and 1 or 2 upper case alphas (in that order).
 
D

Dana DeLouis

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
 
H

Harlan Grove

There will always be a single hyphen followed by up to 3
numeric and 1 or 2 upper case alphas (in that order).
Click to expand...
...

If there's always at least one letter at the end of the part number, try

=RIGHT(PartNo,1+ISNUMBER(FIND(MID(PartNo,LEN(PartNo)-1,1),"0123456789")))
 
J

Jake Marx

Hi Harlan,

Harlan said:
If there's always at least one letter at the end of the part number,
try

=RIGHT(PartNo,1+ISNUMBER(FIND(MID(PartNo,LEN(PartNo)-1,1),"0123456789")))
Click to expand...

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(PartNo)-1,1))))

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
M

Mike Archer

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


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
Click to expand...


.
Click to expand...
 
H

Harlan Grove

...
...
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(PartNo)-1,1))))
Click to expand...

Had meant the latter. Definitely a mental lapse on my part. Appologies to OP.
 
Top