Consulting

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
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    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))
        Next
        
    End Sub

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Good code, mana.
    But I would use spaces around the dash in the Split parameter
    s = Split(c.Value, " - ")
    Or Trim the return
    s = Trim(Split(c.Value, "-"))
    Probably best to Trim the return so some typo leaving out the Space won't kill the code.
    I expect the student to do their homework and find all the errrors I leeve in.


    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
  •