Address Column has unwanted informtion

[pick44]

My addresses have a - then a number after the real number. see attachment.
As example: 3288 - 101 MAIN ST
Should be 328 Main St

Windows 10, Excel 2016

Thanks

[bluesky63]

Hi, if only want to get rid of " - "

Then it will be = SUBSTITUTE(F2, " - ", " ")

if there are other unwanted characters, symbol then will need to use other methods

[bebo021999]

Try:

Please Login or Register  to view this content.

[pick44]

Thank you. The first answer only took the "-" out and not the number that followed. The second formula worked. Thank you

[Bo_Ry]

Please try

=REPLACE(F1,FIND("-",F1&"-"),MATCH(0,-MID(F1&"-0",FIND("-",F1&"-")+1,ROW(A$1:A$9))),)

[pick44]

Yes. it works also. Thanks

[bluesky63]

Hi Bo_Ry, Bebo

if F1 = 3288 - 10122222 MAIN ST

=REPLACE(F1,FIND("-",F1&"-"),MATCH(0,-MID(F1&"-0",FIND("-",F1&"-")+1,ROW(A$1:A$9))),) will be incorrect

3288 2- MAIN ST

For =IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(F1,AGGREGATE(14,6,TRIM(MID(F1,SEARCH("-",F1)+2,{1,2,3,4,5,6,7,8,9}))+0,1),""),"-","")),F1)
if F1 = 3288 - XZA MAIN ST
if will be 3288 - XZA MAIN ST

So really depending on the actually data, there may be other scenarios

Perhaps VBA or Power Query can cover most of the different data cases,

Just my 10 cents comments

[bluesky63]

Hi All,

Just for information some possible solution using Power Query

the custom column

if Text.Contains([Column6],"-") then (Text.BeforeDelimiter([Column6], " ") & "" & Text.AfterDelimiter([Column6], "-",{0, RelativePosition.FromEnd})) else [Column6]

[Bo_Ry]

Hi Bo_Ry, Bebo

if F1 = 3288 - 10122222 MAIN ST

=REPLACE(F1,FIND("-",F1&"-"),MATCH(0,-MID(F1&"-0",FIND("-",F1&"-")+1,ROW(A$1:A$9))),) will be incorrect

3288 2- MAIN ST

Depend on data, Just add some more condition


=SUBSTITUTE(REPLACE(F2,FIND("-",F2&"-"),MATCH(0,-MID(F2&"-0",FIND("-",F2&"-")+1,ROW(A$1:A$99)))+1,),"- ",)