I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle

I think that this will be mostly a manual effort.

Whatever you do, save often and make plenty of backups. One mass change and a
save and you may lose any corrections you've already verified.

You could do this:
select the range
edit|replace
what: - (hyphen)
with: (leave blank)
replace all

32-59 85 Ave
will become
3259 85 Ave

but if that hyphen is used in other portions of the address:

1234 North-West Highway
These may change, too.

As for the spaces, this is more trouble:

32 59 85 Ave
would become
325985Ave
Not what you had in mind.



Lisleb wrote:

I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle

--

Dave Peterson

Unless there is some consistency to the issue this will not be an easy task.

If you are certain that the unwanted hyphens are the ONLY hyphens in your
data, you can insert a new column and use this formula there, copied down for
all the addresses:

=SUBSTITUTE(cell with house #,"-","")

That will get rid of the hyphens. The spaces are a different matter.

One way would be to test if there is more than 2 spaces in an address and
where there are 3 or more use the substitute() function to replace the first:

=if((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))2,SUBSTITUTE(A2," ","",1),a2)

That will work when the problem address is

37 35 Main Street

However, what do you do when the actual address is

3735 East Main Street

I'm afraid you'll have to eyeball the ones with spaces to determine which
ones to fix


"Lisleb" wrote:

I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle

Suppose wrong data are in column A.
Write in an unused helper column, row 2 (supposed you have a header row)

=IF(MID(A2,3,1)="-",SUBSTITUTE(A2,"-",""),IF(MID(A2,3,1)=" ",SUBSTITUTE(A2,"
",""),A2))

and fill down as necessary!

If you need the corrected data in the original column A, copy the helper
column and paste special (values only) to column A!

Regards,
Stefi


€žLisleb€� ezt Ã*rta:

I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle

Sorry, there was a mistake in my message. The right one is:

=IF(MID(A2,3,1)="-",SUBSTITUTE(A2,"-","",1),IF(MID(A2,3,1)="
",SUBSTITUTE(A2," ","",1),A2))

I have read Dave's and Duke's messages but I don't see the problem so hard.
My suggestion will correct the errors of the two types you explicitly
mentioned and won't do anything else. If you find other types of errors, send
a new message, and we shall find the solution.

Regards,
Stefi


€žStefi€� ezt Ã*rta:

Suppose wrong data are in column A.
Write in an unused helper column, row 2 (supposed you have a header row)

=IF(MID(A2,3,1)="-",SUBSTITUTE(A2,"-",""),IF(MID(A2,3,1)=" ",SUBSTITUTE(A2," ","",1),A2))

and fill down as necessary!

If you need the corrected data in the original column A, copy the helper
column and paste special (values only) to column A!

Regards,
Stefi


€žLisleb€� ezt Ã*rta:

I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle

Thanks very much to all of you. Your suggestions worked very well.
Thanks again.
--
lisle


"Stefi" wrote:

Sorry, there was a mistake in my message. The right one is:

=IF(MID(A2,3,1)="-",SUBSTITUTE(A2,"-","",1),IF(MID(A2,3,1)="
",SUBSTITUTE(A2," ","",1),A2))

I have read Dave's and Duke's messages but I don't see the problem so hard.
My suggestion will correct the errors of the two types you explicitly
mentioned and won't do anything else. If you find other types of errors, send
a new message, and we shall find the solution.

Regards,
Stefi


€žStefi€� ezt Ã*rta:

Suppose wrong data are in column A.
Write in an unused helper column, row 2 (supposed you have a header row)

=IF(MID(A2,3,1)="-",SUBSTITUTE(A2,"-",""),IF(MID(A2,3,1)=" ",SUBSTITUTE(A2," ","",1),A2))

and fill down as necessary!

If you need the corrected data in the original column A, copy the helper
column and paste special (values only) to column A!

Regards,
Stefi


€žLisleb€� ezt Ã*rta:

I have some data in excel 03 where addresses are written as 32-59 85 Ave or
32 59 85 Ave. How do I get Excel to correct this data to remove the dash or
the space to express the address as 3259 85 Ave.
Thanks.
--
lisle