Consulting

Results 1 to 18 of 18

Thread: Splitting up a String

  1. #1
    VBAX Newbie
    Joined
    Nov 2019
    Posts
    1
    Location

    Splitting up a String

    Hi All


    I have the following data the in cells A1 and A2


    123456 - Summer 2020 - Norwich
    345667 Holiday - 2019 Liverpool


    How can I via VBA delete all cell data until it gets to the 1st Alpha Character so I would now get


    Summer 2020 - Norwich
    Holiday - 2019 Liverpool


    So effectively the code would look it each cell and when it gets to first alpha Char it would split it this point


    Any help would be appreciated
    Zahed

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Before running the following macro, select the cells you want to be processed:
    Sub blah()
    For Each cll In Selection.Cells
      For i = 1 To Len(cll.Value)
        ac = Asc(Mid(UCase(cll.Value), i, 1))
        If ac > 64 And ac < 91 Then
          cll.Value = Mid(cll.Value, i)
          Exit For
        End If
      Next i
    Next cll
    End Sub
    RegEx would probably be better but I don't know it well enough.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi Saima!
    Using RegExp methods:
    Sub test()
      Dim reg As Object, rng As Range, arr, i&
      arr = Array("123456 - Summer 2020 - Norwich", "345667 Holiday - 2019 Liverpool")
      Set reg = CreateObject("vbscript.regexp")
      With reg
        .Pattern = "^[^A-z]+"
        For i = 0 To 1
          MsgBox .Replace(arr(i), "")
        Next i
      End With
    End Sub

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Option Explicit
    
    Sub RemoveFirst()
    Dim i As Long
    Dim arr
    Dim tmp As String
    Dim Cel As Range
    
    For Each Cel In Selection
    On Error GoTo CelNext
       arr = Split(Cel, " - ")
       For i = LBound(arr) + 1 To UBound(arr)
          tmp = tmp + arr(i)
          If i < UBound(arr) Then tmp = tmp + " - "
       Next i
    CelNext:
    Next Cel
    Cel = tmp
    End Sub
    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

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    a UDF

    Function F_snb(c00)
       F_snb = Mid(c00, Len(CStr(Val(c00))) + 1)
    End Function

    In B1: =F_snb(A1)
    In B2: =F_snb(A2)

    a Macro:

    Sub M_snb()
       sn = Cells(1).CurrentRegion
       
       For j = 1 To UBound(sn)
          sn(j, 1) = Trim(Mid(sn(j, 1), Len(CStr(Val(sn(j, 1)))) + 1))
       Next
       
       Cells(1, 5).Resize(UBound(sn)) = sn
    End Sub

  6. #6
    @snb

    Op want this answer:
    Summer 2020 - Norwich
    Holiday - 2019 Liverpool

    your cod result is:
    - Summer 2020 - Norwich
    Holiday - 2019 Liverpool

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Then you or TS should amend the code.

  8. #8
    snb,

    If you help someone don't ask for "money"

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    We don't provide solutions in Fora, only suggestions to stimulatie TS to find her/his own approach.

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    @Saima, if your solution will always have a " - " after a set of numbers and before the text you want, SamT's solution will work just fine. Don't mind snb, they're always cantankerous.

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    For those who lacked the imagination:

    Sub M_snb()
      sn = Cells(1).CurrentRegion
       
      For j = 1 To UBound(sn)
        sn(j, 1) = Replace(Trim(Replace(Mid(sn(j, 1), Len(CStr(Val(sn(j, 1)))) + 1), " - ", "   ")), "   ", " - ")
      Next
       
      Cells(1, 5).Resize(UBound(sn)) = sn
    End Sub
    also applicable to the UDF.

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    For those who lacked the proper imagination, we would add this:

        Dim sn As Variant
        Dim j As Long

  13. #13
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Now now boys
    Semper in excretia sumus; solum profundum variat.

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Zack, how about being really pedantic and asking for some decent Error Trapping while you are at it.
    And what about the possibility of Empty Cells, will they break the code?
    Incorrect use of Null?

  15. #15
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    @OBP: what is this madness you speak of?!?! Heresy!!

  16. #16
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    @OBP:
    decent Error Trapping while you are at it.
    And what about the possibility of Empty Cells, will they break the code?
    I thought I handled all that. No?
    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

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sam, it wasn't aimed at you.

    Zack, How are you doing, we haven't spoken in years.
    Are you still a first responder?

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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

Posting Permissions

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