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 in Database by Edureka
• 9,320 points
48 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 by Edureka
• 8,820 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 in Database by Edureka
• 8,820 points
55 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 in Database by Edureka
• 9,320 points
27 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 in Database by Edureka
• 8,820 points
50 views
0 votes
1 answer

Remove duplicates within a row

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

answered Mar 15 in Database by Edureka
• 8,820 points
32 views
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 in Database by Edureka
• 8,820 points
49 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 in Database by Edureka
• 8,820 points
28 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP