Results 1 to 4 of 4

Thread: Break text

  1. #1

    Break text

    Good Morning,

    I have some data below

    A1 -> TEST - 123 - 965A

    A2 -> TEST - 111 - 656 - 965ASD

    I need to break it so it looks like this

    B1 -> 965A
    B2 -> 965ASD

    I need to fetch the 20 characters after the last "-"

    Can be VBA or formula.

  2. #2
    Option Explicit 
    Sub test() 
        Dim c As Range 
        Dim s 
        For Each c In Columns(1).SpecialCells(2) 
            s = Split(c.Value, "-") 
            c.Offset(, 1).Value = s(UBound(s)) 
    End Sub 
    Formatting tags added by mark007

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    Good code, mana.
    But I would use spaces around the dash in the Split parameter
    s = Split(c.Value, " - ") 
    Formatting tags added by mark007
    Or Trim the return
    s = Trim(Split(c.Value, "-")) 
    Formatting tags added by mark007
    Probably best to Trim the return so some typo leaving out the Space won't kill the code.
    Please take the time to read the Forum FAQ

  4. #4
    Good afternoon,

    Thank you very much, it was very good.

Posting Permissions

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