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)))

Raw Data	Clean Data
B14- Jose $250 12/06/16 OTR	Jose $250 OTR
B15- 250 Josefa otr 1/05/29 Mac	250 Josefa otr Mac
B16- 12/25/89 OTR Windows $152	OTR Windows $152
B17- Ana OTR Windows $152	Ana OTR Windows $152 Ana OTR Windows $152

<tbody>
</tbody>

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