Cut off end of a string

O

Oldjay

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
 
R

Ron Rosenfeld

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

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
 
O

Oldjay

I should have said "I am copying a string"

Ron Rosenfeld said:
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
Click to expand...
 
O

Oldjay

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
 
R

Ron Rosenfeld

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

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
 
R

Ron Rosenfeld

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
 
O

Oldjay

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
 
R

Ron Rosenfeld

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

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