VBA required to split some specific end word to another column

**amb2301** · 08-11-2020, 08:58 AM

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.

**Haluk** · 08-11-2020, 09:47 AM

May be this;

Please Login or Register  to view this content.

**KOKOSEK** · 08-11-2020, 09:54 AM

Close. It not catch E at start in E 20th Ave.

**amb2301** · 08-11-2020, 09:54 AM

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

**Bo_Ry** · 08-11-2020, 10:26 AM

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.

**amb2301** · 08-11-2020, 10:47 AM

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.

**amb2301** · 08-11-2020, 01:10 PM

could someone please help me to complete it

**Bo_Ry** · 08-11-2020, 01:26 PM

Please try

Please Login or Register  to view this content.

**amb2301** · 08-11-2020, 01:37 PM

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

**Haluk** · 08-11-2020, 05:26 PM

You can try this RegExp as well;

Please Login or Register  to view this content.

**amb2301** · 08-11-2020, 05:33 PM

Thank you so much Haluk

**Haluk** · 08-11-2020, 05:34 PM

You are welcome...

VBA required to split some specific end word to another column

LinkBack

Thread Tools

Rate This Thread

Display

VBA required to split some specific end word to another column

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Split Last word into separate column.

Split text in a column using an entire word and not just a single character

Split text in a column using an entire word and not just a single character

Split string with no delimiters to grab last word as numerical value in new column

[SOLVED] Split words to another column from chosen word

Having problem with text to column split on a particular word

Split cell so that the last word in separate column

Bookmarks

Bookmarks

Posting Permissions