Drop a specific letter off of words

  • Thread starter Thread starter BTU_needs_assistance_43
  • Start date Start date
B

BTU_needs_assistance_43

I'm decently well versed in the Left( string, Len( String ) - 1) command but
how do I set the program so that it will only run this command IF the word
ends in the letter "a"? I'm pulling shot names out of Excel files and while
the 2nd and 3rd always end in "b" and "c", the first sometimes ends in "a"
and sometimes doesn't depending on who makes the report...

ex: ow23 rln24a boltz25a
ow23b rln24b boltz25b
ow23c rln24c

So I need to figure out how to make my program detect if that last letter is
an "a" or not. For database purposes, my program will lop off the last digit
if there so that all the shots from one area can be totaled up and this can
only be done if they are all identical in name. Because the names come in
inconsistent lengths I can't just count the number of letters in name to know
when I need to chop a name short or not. I can't do it manually either
because there are hundreds and hundreds of files and more added every day.

I have the loose idea for a program that will compare the length of the
first word to that of the second word and if they are the same it will chop
one letter off of all the names and if not it will only chop the last letter
off the second two, but im having a problem getting the program to run.

If vShotb <> ("10??") Then
If Len(vShots) = Len(vShotb) Then
Left([vShots], Len(vShots) - 1) =
Left([vShotb], Len(vShotb) - 1) =
If vShotc <> ("10??") Then
Left([vShotc], Len(vShotc) - 1) =
Else
End If
Else
End If

Left([vShotb], Len(vShotb) - 1) =
If vShotc <> ("10??") Then
Left([vShotc], Len(vShotc) - 1) =
Else
End If
Else
 
The Replace function may come in handy here, just be sure to start it on the
last character of the string.

String = Replace(String, "a", "", Len(String))

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
If you want to chop the last character if it is NOT a number then

IF vShot like "*[!0-9]" Then
vShot = Left(vShot,Len(vShot)-1)
End If


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top