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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,671
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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
    434
    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 Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    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 always 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 Guru
    Joined
    Apr 2012
    Posts
    4,660
    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 Guru
    Joined
    Apr 2012
    Posts
    4,660
    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 Guru
    Joined
    Apr 2012
    Posts
    4,660
    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,897
    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 Guru
    Joined
    Apr 2012
    Posts
    4,660
    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,897
    Location
    For those who lacked the proper imagination, we would add this:

        Dim sn As Variant
        Dim j As Long

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

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,092
    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,897
    Location
    @OBP: what is this madness you speak of?!?! Heresy!!

  16. #16
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    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 always 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,092
    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 Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,014
    Location
    I always 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
  •