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

2
votes

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

3
excelexcel-formula

3 Answers

4
votes

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

Another solution:

enter image description here

Formula:

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

0
votes

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

1

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

2

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