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.

 

[indiantrix]

Check your "double quotation marks" and ensure that they do, indeed, have a space character in between them. HTH - Larry.

 

[Sandeep Warrier]

Hi

Try

Excel Workbook
	A	B	C	D	E
2	2100 Streetname Ave City Province L6L 1E3		2100 Streetname Ave City	Province	L6L 1E3
3	2300 Streetname Ave City Cityname2 Province L6L 1E6		2300 Streetname Ave City Cityname2	Province	L6L 1E6
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=TRIM(SUBSTITUTE(SUBSTITUTE(A2,D2,""),E2,""))
D2	=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,E2,""))," ",REPT(" ",50)),50))
E2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100))

How do you plan to separate the streetname from this? Does the street name always contain a number followed by text followed by drive or avenue?

 

[bosco_yip]

A bit shorten of Sandeep's formula :


1] C2 :

=SUBSTITUTE(A2," "&D2&" "&E2,"")

2] D2 :

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," "&E2,"")," ",REPT(" ",50)),50))

3] E2 :

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),99))

Regards

 

[tpohara]

Sandeep thanks...That is great. However I am having a problem with your formula, that I also encountered earlier, and was the very reason I had created the Concatenated filed (apparently not the most suitable workaround).

The city data (the city i live in) contains "TORONTO" and the Province is "ON'.

When the data is processed. It omits the ON in every filed that contains ON data. So it starts like this:

2789 MCMOUSIFE AVE TORONTO ON M7R 7P2

And ends up processed like this:

2789 MCMOUSIFE AVE TORTO ON M7R 7P2

I am using Excel 2003. I truly appreciate the feedback. Any thoughts?

 

[bosco_yip]

Hi tpohara,

Could you try my formula posted on #4, you would find the result meet your expected.

Regards

 

[scottylad2]

Why are you nut using space as the delimiter?

 

[Sandeep Warrier]

Sandeep thanks...That is great. However I am having a problem with your formula, that I also encountered earlier, and was the very reason I had created the Concatenated filed (apparently not the most suitable workaround).

The city data (the city i live in) contains "TORONTO" and the Province is "ON'.

When the data is processed. It omits the ON in every filed that contains ON data. So it starts like this:

2789 MCMOUSIFE AVE TORONTO ON M7R 7P2

And ends up processed like this:

2789 MCMOUSIFE AVE TORTO ON M7R 7P2

I am using Excel 2003. I truly appreciate the feedback. Any thoughts?

The modification Bosco posted should take care of this scenario....

 

[tpohara]

Bosco, thanks for the reply I did try your formula as well, the formula for E2 worked well, but D2 returns an empty result (Blank).

I then changed the Formula for D2 from:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," "&E2,"")," ",REPT(" ",50)),50))

to:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," "&E2,"")," ",REPT(" ",50)),150))

and it worked (thank you).

The formula for C2, returns the original value (or does not return the text without the C2 and D2 results. Thoughts. I truly appreciate your feedback.

 

[Sandeep Warrier]

Excel Workbook
	A	B	C	D	E
1	2789 MCMOUSIFE AVE TORONTO ON M7R 7P2		2789 MCMOUSIFE AVE TORONTO	ON	M7R 7P2
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=TRIM(SUBSTITUTE(A1,D1&" "&E1,""))
D1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,E1,"")," ",REPT(" ",50)),100))
E1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))