Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
    :=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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •