# VBA required to delimit cells with some rules applied on it

1. ## 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.  Register To Reply

2. ## 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&, S, C%         V = Application.Trim(Range("A2", [A1].End(xlDown)))         ReDim W(1 To UBound(V), 3)     For R = 1 To UBound(V)            S = Split(V(R, 1))         If IsNumeric(S(0)) Then                 W(R, 0) = S(0)             If Len(S(1)) = 1 Then                 W(R, 1) = S(1):  W(R, 2) = S(2):  W(R, 3) = S(3)             ElseIf Len(S(UBound(S))) = 1 Then                 W(R, 1) = S(UBound(S)):  W(R, 2) = S(1):  W(R, 3) = S(2)             Else                 If UBound(S) = 3 Then W(R, 2) = S(1) & " " & S(2) Else W(R, 2) = S(1)                 W(R, 3) = S(UBound(S))             End If         Else            W(R, 0) = Left(S(0), Len(S(0)) - 1):  W(R, 1) = Right(S(0), 1):  W(R, 2) = S(1): W(R, 3) = S(2)         End If     Next         [B2:E2].Resize(R - 1) = W End Sub  ```
► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄  Register To Reply

3. ## Re: Hi ! Try this demonstration ‼ Originally Posted by Marc L According to your attachment as a VBA beginner starter (you can easily amend if necessary) :

PHP Code:
``` Sub Demo1()     Dim V(), W(), R&, S, C%         V = Application.Trim(Range("A2", [A1].End(xlDown)))         ReDim W(1 To UBound(V), 3)     For R = 1 To UBound(V)            S = Split(V(R, 1))         If IsNumeric(S(0)) Then                 W(R, 0) = S(0)             If Len(S(1)) = 1 Then                 W(R, 1) = S(1):  W(R, 2) = S(2):  W(R, 3) = S(3)             ElseIf Len(S(UBound(S))) = 1 Then                 W(R, 1) = S(UBound(S)):  W(R, 2) = S(1):  W(R, 3) = S(2)             Else                 If UBound(S) = 3 Then W(R, 2) = S(1) & " " & S(2) Else W(R, 2) = S(1)                 W(R, 3) = S(UBound(S))             End If         Else            W(R, 0) = Left(S(0), Len(S(0)) - 1):  W(R, 1) = Right(S(0), 1):  W(R, 2) = S(1): W(R, 3) = S(2)         End If     Next         [B2:E2].Resize(R - 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   Register To Reply

4. ## Re: Hi ! Try this demonstration ‼

Hi Marc L, Attachment 678028
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)

B cell : Door#
C cell : Direction  Register To Reply

5. ## 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),  Register To Reply

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

Cross-posted at: https://www.excelguru.ca/forums/show...pplied-over-it  Register To Reply