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

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

Feb 22, 2022 in Database by Edureka
• 13,690 points
666 views

1 answer to this question.

0 votes
Select the row or column that you want to split,
Now select the DATA > TEXT TO COLUMN
There is an option to convert text to column wizard here you need to select Delimited> next
You can use delimiters for your data and select, then select the destination which is where you can split your data
then select finish.
You'll be able to split the data
answered Feb 23, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

How to deal with "Microsoft Excel is waiting for another application to complete an OLE action"

The first step in troubleshooting is to ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
1,495 views
0 votes
1 answer
0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24, 2022 in Database by Edureka
• 13,690 points
1,983 views
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,545 views
0 votes
1 answer

Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
600 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
4,776 views
0 votes
1 answer

EXCEL How to check if Column A contains specific value and Column B contains 2 specific values?

If you're looking for the string CHECK in cells ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
583 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP