Reputation: 535

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

Upvotes: 3

Answers (3)

Reputation: 8220

Another solution:

Formula:

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

Upvotes: 1

Reputation: 5902

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

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

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

Upvotes: 0

Reputation: 34210

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

Upvotes: 5