Duplicates values when using the "&" to join results

[Jonathan1726]

Hello Guys, here is the situation:
I created a formula to extract the date on a string of text from any position, and I used the IF function to show the same value is the Date is not in the text.

Here is the example:

FORMULA:
=TRIM(IF(ISERROR(SEARCH("/",B14)),B14,LEFT(B14,SEARCH("/",B14)-3))&" "&IF(ISERROR(SEARCH("/",B14)),B14,RIGHT(B14,LEN(B14)-SEARCH("/",B14)-5)))

[TABLE="width: 591"]
<tbody>[TR]
[TD] Raw Data[/TD]
[TD] Clean Data[/TD]
[/TR]
[TR]
[TD]B14- Jose $250 12/06/16 OTR[/TD]
[TD] Jose $250 OTR[/TD]
[/TR]
[TR]
[TD]B15- 250 Josefa otr 1/05/29 Mac[/TD]
[TD] 250 Josefa otr Mac[/TD]
[/TR]
[TR]
[TD]B16- 12/25/89 OTR Windows $152[/TD]
[TD] OTR Windows $152[/TD]
[/TR]
[TR]
[TD]B17- Ana OTR Windows $152[/TD]
[TD] Ana OTR Windows $152 Ana OTR Windows $152 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The problem is that because I used the & to join the results, it is repeating the results when it does not find a date on the text (see B17 Underlined). How can I fix this?

 

[Fluff]

Hi & welcome to MrExcel.
Try
=TRIM(IF(ISERROR(SEARCH("/",B14)),B14,LEFT(B14,SEARCH("/",B14)-3))&" "&IF(ISERROR(SEARCH("/",B14)),"",RIGHT(B14,LEN(B14)-SEARCH("/",B14)-5)))

 

[bosco_yip]

Or,

=IFERROR(TRIM(REPLACE(B14,FIND("/",B14)-2,8,"")),B14)

Regards
Bosco

 

[bosco_yip]

In consider the data may have : "1/25/89 OTR Windows $152"

then, the above formula change to :

=IFERROR(TRIM(REPLACE(B14,MAX(1,FIND("/",B14)-2),8,"")),B14)

Regards
Bosco

 

[Jonathan1726]

It worked, thank you very much! this is actually my first time in this forum. I really appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback