+ Reply to Thread
Results 1 to 12 of 12

VBA required to split some specific end word to another column

  1. #1
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    VBA required to split some specific end word to another column

    Hi Friends,
    i have a small challenge in my work, actually i need to move some specific words(N, E, S, W, NE, SE, NW, SW), which appears at the start & end of the street names has to splitted from G column to I column
    as shown in below screen shot (screenshot shown yellow highlighted has to be removed from G column & moved to I column)

    Thanks in Advance.
    Last edited by amb2301; 08-11-2020 at 01:38 PM. Reason: update

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    793

    Re: VBA required to split some specific end word to another column

    May be this;

    Please Login or Register  to view this content.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,158

    Re: VBA required to split some specific end word to another column

    Close. It not catch E at start in E 20th Ave.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    Re: VBA required to split some specific end word to another column

    Thank you so much Haluk it worked perfectly.

    Diections at the End of the Addresses are moving perfectly....but the directions comes at the starting is not moving.
    could you please help me on that part

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: VBA required to split some specific end word to another column

    Please try formula

    =IFNA(LOOKUP("Z",VLOOKUP(TRIM(MID(SUBSTITUTE(G3," ",REPT(" ",50)),{0,1,2,3,4,5}*50+1,50)),{"N";"E";"S";"W";"NE";"SE";"NW";"SW"},1,)),"")

    or VBA

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    Re: VBA required to split some specific end word to another column

    Thank you so much Bo_Ry, its working fine,
    just i noticed it, instead of copy paste the direction, i need the directions(N, E, S, W, NE, SE, NW, SW) to be cut from G column & paste in I column

    Thanks in Advance.

  7. #7
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    Re: VBA required to split some specific start & end word to another column

    could someone please help me to complete it

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: VBA required to split some specific end word to another column

    Please try

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    Re: VBA required to split some specific end word to another column

    Thank you so Much Bo_Ry, it was perfect code!!!
    you saved me
    if possible could you please help me to understand the code, by putting some comments on it.....it will be helpful for my learning



    Your Youtube channel was awesome keep doing

  10. #10
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    793

    Re: VBA required to split some specific end word to another column

    You can try this RegExp as well;

    Please Login or Register  to view this content.
    Last edited by Haluk; 08-12-2020 at 03:22 AM. Reason: typo...

  11. #11
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    196

    Re: VBA required to split some specific end word to another column

    Thank you so much Haluk

  12. #12
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    793

    Re: VBA required to split some specific end word to another column

    You are welcome...

+ 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. Split Last word into separate column.
    By ankit_9090 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2019, 06:52 PM
  2. Replies: 4
    Last Post: 09-26-2019, 03:13 PM
  3. Split text in a column using an entire word and not just a single character
    By hisuh1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2019, 02:31 PM
  4. Replies: 3
    Last Post: 11-16-2018, 04:17 AM
  5. [SOLVED] Split words to another column from chosen word
    By unley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2016, 10:14 PM
  6. Having problem with text to column split on a particular word
    By alicia ch in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2012, 12:29 PM
  7. Split cell so that the last word in separate column
    By CameronP in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-03-2012, 02:01 AM

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