Formula to copy parts of an address in cells and paste in specific columns. - See att

Hi all,

I'm looking for a formula to copy parts of an address in cells and paste in specific columns.

I attach a copy of a before and after (how i'd like it to look) sheet 1 - sheet 2.

Hope someone clever out there can help me with this.

Many Thanks

Marc

I don't think you will find a formula to help you. I think VBA would be the best way however there is inconsistencies with the data.
For the first line it is no problem - you can split the value in A1 with the delimited "-"
Example code

Dim fLine
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
myarr = ws.Range("A1").CurrentRegion
x = 1
fLine = Split(myarr(x, 1), "-")
ws2.Range("A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1) = fLine
x = x + 1

The problem is the 2nd and 4th row of data
Canolfan Siopa / Shopping Centre Bettws Casnewydd / Newport NP20 6TN Ebost / Email: bettws.library@newport.gov.uk
Coldbath Road Casnewydd / Newport NP18 1NF Ebost / Email: caerleon.library@newport.gov.uk
You could split this by the delimiter "/"

fLine = Split(myarr(x, 1), "/")

The problem is that in the array fLine with the 2nd row of data is that there is 4 parts. If you use the same code on the 4th line there is only 3 parts. The logic is not consistent in that to write code to separate the data in the array from the 2nd row of data there is 4 items however only 3 items in the 4th row of data. What logic could you give us to know that "Canolfan Siopa / Shopping Centre Bettws Casnewydd" gets pasted into Column C in sheet 2 however only "Coldbath Road Casnewydd" gets pasted into column C in sheet 2. Will the data that goes into sheet 2 column C always end with "dd" ? If so then that logic could be used to do the splitting of the strings and pasting into the second sheet.
If you could give some logic to determine what part of the string gets pasted where into sheet 2 then code could be written however without some definite logic it is almost impossible. You only give 2 examples so I was unsure the "dd" was a co-incidence or a true marker of the end of a set of data.
Hope this makes sense

Anthony

Hi Anthony,

Are you able to help me with this one. This is alot easier i think thant the previous one.

I attach a copy of a before and after (how i'd like it to look) sheet 1 - sheet 2.

Many thanks

Marc

Hi Anthony,

Many thanks for that, I've had a look at all the other data i have there is no consistency what so ever.

I shall send you something else you might be able to help me with.

hi Marc, option to make it basing on the example file provided, run code "test"

Hi Anton,

Sorry I have very limited use of VB, are you able to insert the code in the document Sample data for community halls yell.

Note - I will add more to this list as well. So the code needs to cater for more information, it will be in the same format as well.

Your help is much appreciated.

Marc

Faaaaaaaaaaaaantastic. Thanks