Trim / Substitute / Rightfunction question

tpohara

New Member
Joined
Sep 6, 2010
Messages
6
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Check your "double quotation marks" and ensure that they do, indeed, have a space character in between them. HTH - Larry.
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Hi

Try
Excel Workbook
ABCDE
22100 Streetname Ave City Province L6L 1E32100 Streetname Ave CityProvinceL6L 1E3
32300 Streetname Ave City Cityname2 Province L6L 1E62300 Streetname Ave City Cityname2ProvinceL6L 1E6
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Sep 6, 2010
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
Hi tpohara,

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

Regards
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

Why are you nut using space as the delimiter?
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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

New Member
Joined
Sep 6, 2010
Messages
6
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

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Excel Workbook
ABCDE
12789 MCMOUSIFE AVE TORONTO ON M7R 7P22789 MCMOUSIFE AVE TORONTOONM7R 7P2
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C1=TRIM(SUBSTITUTE(A1,D1&" "&E1,""))
D1=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,E1,"")," ",REPT(" ",50)),100))
E1=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,313
Messages
5,527,952
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top