+ Reply to Thread
Results 1 to 6 of 6

VBA required to delimit cells with some rules applied on it

  1. #1
    Registered User
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    29

    VBA required to delimit cells with some rules applied on it

    Hi Friends,

    I have task to delimit the addresses provided in CellS A:A, i have split in the format of
    st number(B Cell),Direction(C Cell),st name(D Cell) & st type(E Cell),

    Here i having complexity in Directions(N,E,S,W)... where it comes next to st number sometimes & at the end of the address sometime,
    also sometime attached with st number itself, i m manually cutting & pasting it in C cell,
    for better understanding i have attached my working sheet, could someone help me complete this task,
    i have thousands of address to convert it.


    Thanks in Advance.
    Last edited by amb2301; 05-13-2020 at 09:30 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,067

    Cool Hi ! Try this demonstration ‼


    According to your attachment as a VBA beginner starter (you can easily amend if necessary) :

    PHP Code: 
    Sub Demo1()
        
    Dim V(), W(), R&, SC%
            
    Application.Trim(Range("A2", [A1].End(xlDown)))
            
    ReDim W(1 To UBound(V), 3)
        For 
    1 To UBound(V)
               
    Split(V(R1))
            If 
    IsNumeric(S(0)) Then
                    W
    (R0) = S(0)
                If 
    Len(S(1)) = 1 Then
                    W
    (R1) = S(1):  W(R2) = S(2):  W(R3) = S(3)
                ElseIf 
    Len(S(UBound(S))) = 1 Then
                    W
    (R1) = S(UBound(S)):  W(R2) = S(1):  W(R3) = S(2)
                Else
                    If 
    UBound(S) = 3 Then W(R2) = S(1) & " " S(2) Else W(R2) = S(1)
                    
    W(R3) = S(UBound(S))
                
    End If
            Else
               
    W(R0) = Left(S(0), Len(S(0)) - 1):  W(R1) = Right(S(0), 1):  W(R2) = S(1): W(R3) = S(2)
            
    End If
        
    Next
            
    [B2:E2].Resize(1) = W
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon Add Reputation ! ◄ ◄

  3. #3
    Registered User
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    29

    Re: Hi ! Try this demonstration ‼

    Quote Originally Posted by Marc L View Post

    According to your attachment as a VBA beginner starter (you can easily amend if necessary) :

    PHP Code: 
    Sub Demo1()
        
    Dim V(), W(), R&, SC%
            
    Application.Trim(Range("A2", [A1].End(xlDown)))
            
    ReDim W(1 To UBound(V), 3)
        For 
    1 To UBound(V)
               
    Split(V(R1))
            If 
    IsNumeric(S(0)) Then
                    W
    (R0) = S(0)
                If 
    Len(S(1)) = 1 Then
                    W
    (R1) = S(1):  W(R2) = S(2):  W(R3) = S(3)
                ElseIf 
    Len(S(UBound(S))) = 1 Then
                    W
    (R1) = S(UBound(S)):  W(R2) = S(1):  W(R3) = S(2)
                Else
                    If 
    UBound(S) = 3 Then W(R2) = S(1) & " " S(2) Else W(R2) = S(1)
                    
    W(R3) = S(UBound(S))
                
    End If
            Else
               
    W(R0) = Left(S(0), Len(S(0)) - 1):  W(R1) = Right(S(0), 1):  W(R2) = S(1): W(R3) = S(2)
            
    End If
        
    Next
            
    [B2:E2].Resize(1) = W
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon Add Reputation ! ◄ ◄



    Thank you so much Marc L, it worked like a charm!!!
    you are awesome man

  4. #4
    Registered User
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    29

    Re: Hi ! Try this demonstration ‼

    Hi Marc L, Attachment 678028
    Your script worked perfectly for 90% of addresses,
    i need your help here, actually i run the script in some hundreds of addresses, i found some
    issues while running script on different scenario of addresses.
    i have highlighted those issues in the aatched image.
    i tried to edit the script, but not happening for me.

    i understand that confusion happened due to the seperation of streetname & roadtype, so if possible can you please combine streetname & roadtype as a single cell..
    so finally we it will be like (New_address sheet.xlsm)

    A Cell : Addresses
    B cell : Door#
    C cell : Direction
    D cell : StreetName+Roadtype

    Could you please help me to resolve this, as it will be ery helpfull for me.

    Thanks in Advance.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by amb2301; 05-16-2020 at 06:27 PM. Reason: update

  5. #5
    Registered User
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    29

    Re: VBA required to delimit cells with some rules applied on it

    Hi Marc,

    i think i have confused you with my poor explaination in my last thread, sorry for that happening....

    my ask is just not to consider any letters in C column except (N,E,S,W),

    Could you please help me to ammend the script.

    Thanks in Advance.

  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,434

    Re: VBA required to delimit cells with some rules applied on it

    Cross-posted at: https://www.excelguru.ca/forums/show...pplied-over-it
    Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formating with 3 different rules applied
    By suchetherrah in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 12-04-2015, 04:23 AM
  2. [SOLVED] Unable to assign values to categories - Rules based formula help required
    By Aerodynamix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2015, 12:40 PM
  3. Conditional formatting: Applied rules not visible when printing
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2013, 03:32 PM
  4. VBA Code to delimit Column A and Replace cells in Col E
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2013, 06:44 AM
  5. Multi-Worksheet results needed with Rules applied
    By vortexkid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2012, 08:22 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1