Trim / Substitute / Rightfunction question

[tpohara]

I have an excel spreadsheet with address data I am attempting to separate out into separate columns in Excel.

The data is does NOT have any delimiters (like, or ‘), the spreadsheet has 8000 rows so I would like to automate how it breaks up the data. The end plan is to use Excel to create a CSV(or like) file with proper delimiters.

2100 Streetname Ave City Province L6L 1E3
2300 Streetname Ave City Cityname2 Province L6L 1E6

My approach was to breakout the data backwards (starting with postal Code, then Province, then get the city data then break the remaining info up from the beginning)

The main Cell (F3) Has one row of data:
2100 StreetName (could be two fields) Drive City (could be two names) Province L6L 1E3 (Last field being Postal code, which is two fields for example.).

In Cell Q3:
=TRIM(RIGHT(SUBSTITUTE(F3," ",REPT(" ",50)),50))

Result is good : 1E3

In Cell P3:
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(F3," ",REPT(" ",50)),75),Q3,""))

Result is good: L6L

In Cell O3:
=TRIM(CONCATENATE(P3," ",Q3))

Result is good: L6L 1E3

In Cell N3:
=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(F3," ",REPT("”,50)),200),Q3,""),P3,""))

Result is good: Province

In Cell M3: (I create a Concatenated field)
=CONCATENATE(N3," ",O3)

Result is good: Province L6l IE3

When I attempt to strip the combination of Province and Postal code from the address (due to the fact the city etc have more than one name etc) I fail

Problem,

When I attempt to strip out the Province and Postal code in order to process the city names it just will not work. Here is the formula I am using:

In Cell L3:
=TRIM(SUBSTITUTE(F3,F3&" "&M3,""))

Will not strip the data from the main data, no matter what I try I just keep getting the same (original data)

Any ideas? I appreciate your time.

 

[tpohara]

Thanks Sandeep that worked great. I appreciate the effort from everyone.