chop off last 5 characters of a string

[Jeff]

I have a list of strings that vary in length from 10 to 15 characters. The
last 5 characters are always different but always a length of 5 characters
and I want to chop them off and keep only the left part of the string, which
varies in length from 5 to 10 characters. Is there a VBA or SQL or Excel
function that will do this all in one shot for the whole list?

for example, let's say you have the following strings (using numbers as
strings for visual clarity)

12345678905555a
1234567895555b
123456785555c
12345675555d
1234565555e

the function should return

1234567890
123456789
12345678
1234567
123456

The right() function extracts but does not chop off characters and return
the leftmost characters
The left() function won't work because the number of leftmost characters
varies randomly
The trim() functions only work with spaces as far as I know, but it would be
nice if I could use a function like trim to trim off the last 5 characters,
but I don't know if there is one.

Thanks...

Jeff

 

[dymondjack]

how about:

Mid(str, 1, (Len(str) - 5)


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Karl E. Peterson]

I have a list of strings that vary in length from 10 to 15 characters. The
last 5 characters are always different but always a length of 5 characters
and I want to chop them off and keep only the left part of the string, which
varies in length from 5 to 10 characters.

You just gotta get a *little* creative:

Public Function ChopLastFive(ByVal Data As String) As String
If Len(Data) > 5 Then
ChopLastFive = Left$(Data, Len(Data) - 5)
Else
ChopLastFive = ""
End If
End Function

 

[dymondjack]

Mid(str, 1, (Len(str) - 5)

str being your string variable of course, not the Str() function

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Ron Rosenfeld]

I have a list of strings that vary in length from 10 to 15 characters. The
last 5 characters are always different but always a length of 5 characters
and I want to chop them off and keep only the left part of the string, which
varies in length from 5 to 10 characters. Is there a VBA or SQL or Excel
function that will do this all in one shot for the whole list?

for example, let's say you have the following strings (using numbers as
strings for visual clarity)

12345678905555a
1234567895555b
123456785555c
12345675555d
1234565555e

the function should return

1234567890
123456789
12345678
1234567
123456

The right() function extracts but does not chop off characters and return
the leftmost characters
The left() function won't work because the number of leftmost characters
varies randomly
The trim() functions only work with spaces as far as I know, but it would be
nice if I could use a function like trim to trim off the last 5 characters,
but I don't know if there is one.

Thanks...

Jeff

=left(a1, len(a1)-5)
--ron

 

[Alan]

Another way,
=LEFT(A1,LEN(A1)-5)
Drag that down to the end of the list, 'Copy' > 'Paste Special' > 'Values'
to lose the formulas,
Regards,
Alan,

 

[Jeff]

Ah, thanks! I didn't think of that. Ya, I guess that would work with the
left() function as well. I was looking for a function like rtrim() that
would work with all characters and not just spaces.

Thanks for the help!

Jeff

 

[Jeff]

Thanks!

 

[Jeff]

Thanks!

 

[Salmon Egg]

I have a list of strings that vary in length from 10 to 15 characters. The
last 5 characters are always different but always a length of 5 characters
and I want to chop them off and keep only the left part of the string, which
varies in length from 5 to 10 characters. Is there a VBA or SQL or Excel
function that will do this all in one shot for the whole list?

for example, let's say you have the following strings (using numbers as
strings for visual clarity)

12345678905555a
1234567895555b
123456785555c
12345675555d
1234565555e

the function should return

1234567890
123456789
12345678
1234567
123456

The right() function extracts but does not chop off characters and return
the leftmost characters
The left() function won't work because the number of leftmost characters
varies randomly
The trim() functions only work with spaces as far as I know, but it would be
nice if I could use a function like trim to trim off the last 5 characters,
but I don't know if there is one.

Thanks...

Jeff

I do that all the time using Excel. I find it easier to use two cells to
the right of the data column, but it is no big trick to do it using only
one cell. Suppose the data is in cell A6 for example.

Put =LEN(A6) in B6.

Put =LEFT(A6,B6-5) in C6.

I leave it up to you to figure out how to do it in a single cell. You
can then copy and paste to the right of all the data to do the total
conversion.

Bill