Split two different columns with two different addresses

[bengy17]

Hi,

I have attached a file with some data that needs splitting, there is no common delimiter.

I managed to get half way there but then get more issues.

Anyone able to help me. I need to get column D split out into new columns titled Address Line 1, Address Line 2, City, County. Then we need to do exactly the same for Column H, however this will also need to merge into column I as some have postcodes already and some are in the delivery address.

Please help me VBA Kenobi!!

[Aussiebear]

Use the TextSplit function as in select a empty column
For Column D

=TextSplit(D2,",") and copy down
For Column H

=Textsplit(H2,",") and copy down.

[bengy17]

Use the TextSplit function as in select a empty column
For Column D

=TextSplit(D2,",") and copy down
For Column H

=Textsplit(H2,",") and copy down.

Unfortunately this would only work if the delimiter is the same. However, this is not. Delimeter can be not there, and obviously road names can have spaces in for the road name.

[georgiboy]

How about the attached, i have created a lookup on sheet2 that contains the variations of how the (road, close, crescent, way, etc...) end, i have used this to create the below formula:

Address part1:

=LET( ts,TEXTSPLIT(SUBSTITUTE(D2,","," ")," "),
TRIM(TEXTJOIN(" ",,CHOOSECOLS(ts,SEQUENCE(,MIN(TOCOL(MATCH(Sheet2!$A$1:$A$6,ts,0),3))))))
)

Address part2:

=TRIM(SUBSTITUTE(SUBSTITUTE(D2,","," "),E2,""))

[bengy17]

How about the attached, i have created a lookup on sheet2 that contains the variations of how the (road, close, crescent, way, etc...) end, i have used this to create the below formula:

Address part1:

=LET( ts,TEXTSPLIT(SUBSTITUTE(D2,","," ")," "),
TRIM(TEXTJOIN(" ",,CHOOSECOLS(ts,SEQUENCE(,MIN(TOCOL(MATCH(Sheet2!$A$1:$A$6,ts,0),3))))))
)

Address part2:

=TRIM(SUBSTITUTE(SUBSTITUTE(D2,","," "),E2,""))

Was looking for more VBA so user can just copy and paste in, using a lookup against end of roads doesn't work either as some may just be one word.

[georgiboy]

In such a scenario, I can only extend my best wishes to you in your quest to discover a solution.


Based on my background in the transportation industry, I've noticed that the initial lines of most addresses frequently culminate with terms like 'Road,' 'Close,' 'Crescent,' 'Way,' 'Estate,' 'Street,' and similar designations. This pattern is also evident in all the instances within your sample data.


Even when using VBA, I believe it would be essential to utilise a lookup list, as I can't discern a programmatic approach to effectively segment the data.