# Thread: formula to take out all except end (2003)

1. ## formula to take out all except end (2003)

Hello Everyone,

I need some help creating a formula to delete all, but the last numbers at the end. Hans was kind enough to develop the following:
=1*MID(RC[-1],MATCH(FALSE,ISERROR(2*MID(RC[-1],ROW(R1:R51),1)),0),255)
However, I have some cells that may include numbers somewhere in the middle of the cell. For example, I could have the following:
HOnda_Civic_2001
Lexus_RX300_2003
Toyota_2003
I know for a fact the the last number after the underline is what I need to get. Any help would be great. Thanks.

2. ## Re: formula to take out all except end (2003)

If the values will *always* end in an underscore followed by a number, you can use the following array formula (confirm with Ctrl+Shift+Enter):

=1*MID(A1,MAX(ROW(\$1:\$100)*(MID(A1,ROW(\$1:\$100),1) ="_"))+1,255)

3. ## Re: formula to take out all except end (2003)

Hans, one last question, I am trying to write a macro to include this formula and populate the column for all the rows that have data. How can I go about doing this. When I also put the macro for the formula, I get a red ink on the line of coding and it refers back to the "-". Any help would be great.

Sub CleanUpSheet()
'
' CleanUpSheet Macro

'

'
Columns("A").Select
Selection.Delete shift:=xlToLeft
Columns("B:B").Select
Selection.ClearContents
Columns("A:C").Select
Columns("A:C").EntireColumn.AutoFit
Range("B1").Select
ActiveCell.FormulaR1C1 = "PCA"
Columns("A:B").Select
With Selection.Validation
.Delete
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("B2").Select
Selection.FormulaArray = "=1*MID(A1,MAX(ROW(\$1:\$100)*(MID(A1,ROW(\$1:\$100),1 )="_"))+1,255)"
Selection.AutoFill Destination:=Selection
Range("A1").Select
End Sub

4. ## Re: formula to take out all except end (2003)

The problem is that the formula contains quotes. If you want to include quotes inside a quoted string, you must double them, otherwise VBA gets confused.
<code>
Selection.FormulaArray = "=1*MID(A1,MAX(ROW(\$1:\$100)*(MID(A1,ROW(\$1:\$100),1 )=""_""))+1,255)"</code>

5. ## Re: formula to take out all except end (2003)

Got it thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•