# Excel - Split cell with a delimiter and if delimiter not found get complete cell value to another cell

I have an excel file with data where I want a Split cell with a delimiter and if the delimiter is not found then copy that cell value to another cell

e.g.

ABC Ltd.

ABC Ltd. - Ind

PQR Ltd. - USA

LMN Corp.

Output - delimiter "-"

ColumnA ColumnB

ABC Ltd. ABC Ltd.

ABC Ltd. Ind

PQR Ltd. USA

LMN Corp. LMN Corp.

However, I'm using below formula but not getting what I want.

=TRIM(LEFT(B2,FIND("-",B2)-1))

=TRIM(REPLACE(B2,1,FIND("-",B2),"")) On Best Solutions

You need to put in error handling for the case where B2 doesn't contain a '-'

``````=IFERROR(TRIM(LEFT(B2,FIND("-",B2)-1)),TRIM(B2))
``````

and

``````=IFERROR(TRIM(REPLACE(B2,1,FIND("-",B2),"")),TRIM(B2))
`````` On

Alternative approach (doesn't necessarily mean better though ;) )

# 1

``````=TRIM(LEFT(B2&"-",FIND("-",B2&"-",1)-1))
``````

# 2

``````=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",99)),99))
`````` On

Another solution: Formula:

`=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))>0,RIGHT(A1,LEN(A1)-FIND("-",A1,1)-1),A1)`