Cut off end of a string
 

When I import a string to paste I want to check the end of the string and if
the last 2 letters match one from a list then I want to delete them before I
pastea the string.
oldjay

Cut off end of a string
 

On Tue, 3 Mar 2009 12:02:05 -0800, Oldjay
wrote:

When I import a string to paste I want to check the end of the string and if
the last 2 letters match one from a list then I want to delete them before I
pastea the string.
oldjay

I'm not sure how you are "importing" without "pasting". Can you explain?

If you have a string in a cell, you can check to see if the last two characters
are in a "SuffixList" with this **array** formula:

=LEFT(A1,LEN(A1)-2*OR(RIGHT(A1,2)=SuffixList))

where SuffixList is the range where you have your list.

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

You can then edit/copy Edit/Paste Special -- Values and then delete the
original import.
--ron

Cut off end of a string
 

I should have said "I am copying a string"

"Ron Rosenfeld" wrote:

On Tue, 3 Mar 2009 12:02:05 -0800, Oldjay
wrote:

When I import a string to paste I want to check the end of the string and if
the last 2 letters match one from a list then I want to delete them before I
pastea the string.
oldjay

I'm not sure how you are "importing" without "pasting". Can you explain?

If you have a string in a cell, you can check to see if the last two characters
are in a "SuffixList" with this **array** formula:

=LEFT(A1,LEN(A1)-2*OR(RIGHT(A1,2)=SuffixList))

where SuffixList is the range where you have your list.

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

You can then edit/copy Edit/Paste Special -- Values and then delete the
original import.
--ron

Cut off end of a string
 

This is my code

Private Sub CommandButton22_Click() 'Recalls a quote

Dim quotenumber As String
Dim QUOTE As String

Dim user As String
user = Application.UserName
MainMenu.Hide

Application.ScreenUpdating = False 'Keeps screen from updating thereby
speeding routine
Application.DisplayAlerts = False

quotenumber = InputBox("Please enter QUOTE number to recall from your
hard drive. If recalling a quote from the server" & _
"type in \\server3\jobs\estimate1\quickquotes3\ and the quote number.")
QUOTE = "C:\Quick Quotes3\" & quotenumber & ".XLS"
On Error GoTo ehandler

Workbooks.Open Filename:=QUOTE
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("TempData.XLS").Activate 'Recalls input saved data
Range("A2:A36").Select
Selection.Copy

This is were I want to check the last 2 letters

Windows(MasterSheet).Activate
Range("AB2").Select
Selection.PasteSpecial Paste:=xlValues

etc

"Ron Rosenfeld" wrote:

On Tue, 3 Mar 2009 12:02:05 -0800, Oldjay
wrote:

When I import a string to paste I want to check the end of the string and if
the last 2 letters match one from a list then I want to delete them before I
pastea the string.
oldjay

I'm not sure how you are "importing" without "pasting". Can you explain?

If you have a string in a cell, you can check to see if the last two characters
are in a "SuffixList" with this **array** formula:

=LEFT(A1,LEN(A1)-2*OR(RIGHT(A1,2)=SuffixList))

where SuffixList is the range where you have your list.

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

You can then edit/copy Edit/Paste Special -- Values and then delete the
original import.
--ron

Cut off end of a string
 

On Tue, 3 Mar 2009 15:39:36 -0800, Oldjay
wrote:

This is my code

Private Sub CommandButton22_Click() 'Recalls a quote

Dim quotenumber As String
Dim QUOTE As String

Dim user As String
user = Application.UserName
MainMenu.Hide

Application.ScreenUpdating = False 'Keeps screen from updating thereby
speeding routine
Application.DisplayAlerts = False

quotenumber = InputBox("Please enter QUOTE number to recall from your
hard drive. If recalling a quote from the server" & _
"type in \\server3\jobs\estimate1\quickquotes3\ and the quote number.")
QUOTE = "C:\Quick Quotes3\" & quotenumber & ".XLS"
On Error GoTo ehandler

Workbooks.Open Filename:=QUOTE
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("TempData.XLS").Activate 'Recalls input saved data
Range("A2:A36").Select
Selection.Copy

This is were I want to check the last 2 letters

Windows(MasterSheet).Activate
Range("AB2").Select
Selection.PasteSpecial Paste:=xlValues

etc

You could use the same algorithm as in the worksheet function with something
like:

=========================
....
For Each c In Range("AB2:AB36")
Select Case Right(c.Value, 2)
Case "AB", "CD", "EF" 'etc
c.Value = Left(c.Value, Len(c.Value) - 2)
End Select
Next c
....
--ron

Cut off end of a string
 

Sorry I didn't show the correct code. I have edited the code below


"Ron Rosenfeld" wrote:

On Tue, 3 Mar 2009 15:39:36 -0800, Oldjay
wrote:

This is my code

Private Sub CommandButton22_Click() 'Recalls a quote

Dim quotenumber As String
Dim QUOTE As String

Dim user As String
user = Application.UserName
MainMenu.Hide

Application.ScreenUpdating = False 'Keeps screen from updating thereby
speeding routine
Application.DisplayAlerts = False

quotenumber = InputBox("Please enter QUOTE number to recall from your
hard drive. If recalling a quote from the server" & _
"type in \\server3\jobs\estimate1\quickquotes3\ and the quote number.")
QUOTE = "C:\Quick Quotes3\" & quotenumber & ".XLS"
On Error GoTo ehandler

Workbooks.Open Filename:=QUOTE
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("TempData.XLS").Activate 'Recalls quote number
Range("A2").Select
Selection.Copy

This is were I want to check the last 2 letters against a list of initials. If they are on the list then delete them.

Windows(MasterSheet).Activate
Range("AB2").Select
Selection.PasteSpecial Paste:=xlValues

etc

You could use the same algorithm as in the worksheet function with something
like:

=========================
....
For Each c In Range("AB2:AB36")
Select Case Right(c.Value, 2)
Case "AB", "CD", "EF" 'etc
c.Value = Left(c.Value, Len(c.Value) - 2)
End Select
Next c
....
--ron

Cut off end of a string
 

On Tue, 3 Mar 2009 18:25:02 -0800, Oldjay
wrote:

For Each c In Range("AB2:AB36")
Select Case Right(c.Value, 2)
Case "AB", "CD", "EF" 'etc
c.Value = Left(c.Value, Len(c.Value) - 2)
End Select
Next c

Well, if you're only checking AB2, then simply:

with range("ab2")
select case right(.value,2)
case "ab", "cd", "ef" 'etc
.value = left(.value,len(.value)-2)
end select
end with
--ron

Cut off end of a string
 

The list is can change. Is there a way to use a range?
Thanks for all the help. You guys make life so much easier for us dumb guys.

oldjay

"Ron Rosenfeld" wrote:

On Tue, 3 Mar 2009 18:25:02 -0800, Oldjay
wrote:

For Each c In Range("AB2:AB36")
Select Case Right(c.Value, 2)
Case "AB", "CD", "EF" 'etc
c.Value = Left(c.Value, Len(c.Value) - 2)
End Select
Next c

Well, if you're only checking AB2, then simply:

with range("ab2")
select case right(.value,2)
case "ab", "cd", "ef" 'etc
.value = left(.value,len(.value)-2)
end select
end with
--ron

Cut off end of a string
 

On Wed, 4 Mar 2009 06:36:01 -0800, Oldjay
wrote:

The list is can change. Is there a way to use a range?
Thanks for all the help. You guys make life so much easier for us dumb guys.

oldjay

If you want to use a list on your worksheet, you could do something like:


---------------------
....
Dim c As Range
Dim Suffix As String
With Range("AB2")
Suffix = Right(.Value, 2)
For Each c In Range("SuffixList")
If Suffix = c.Value Then
.Value = Left(.Value, Len(.Value) - 2)
Exit For
End If
Next c
End With
....
--------------------

Note that I used a NAME'd range for the list. You could use a hard coded
range, if you preferred.
--ron