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

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


"Jeff" wrote:

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

Jeff wrote:
> 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

--
..NET: It's About Trust!
http://vfred.mvps.org

> 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


"dymondjack" wrote:

> 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
>
>
> "Jeff" wrote:
>
> > 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
> >
> >

On Tue, 3 Mar 2009 17:31:52 -0800, "Jeff" <(E-Mail Removed)> wrote:

>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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

"dymondjack" <dymondjack at hot mail dot com> wrote in message
news:1CD337BD-B79D-43FA-B83C-(E-Mail Removed)...
> how about:
>
> Mid(str, 1, (Len(str) - 5)
>
>
> --
> Jack Leach

Thanks!

"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> =left(a1, len(a1)-5)
> --ron

Thanks!

"Alan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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,

In article <(E-Mail Removed)>,
"Jeff" <(E-Mail Removed)> wrote:

> 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

--
Private Profit; Public Poop! Avoid collateral windfall!