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
|