CODEWITHSUNDEEP
excel
banme
banme

Reputation: 7

how to split duble value in tow rows in excel

Hello in my excel sheet i have two same value in one cell. I want to split them. for ex:

Cell A                                    | Cell B       | Cell C
===============================================================
my name is demo my name is demo user      |my name is demo|my name is demo user
----------------------------------------------------------------
my first name my first name and last name | my first name |my first name and last name

I have tried text to columns tool but not got luck. Any idea about this thanks.

Upvotes: 1

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

The first formula in B1:

=TRIM(LEFT(A1,FIND(LEFT(A1,FIND(" ",A1)),A1,LEN(LEFT(A1,FIND(" ",A1)))+1)-1))

This finds the second occurrence of the first word and splits on the character before that.

The second formula in C1:

=TRIM(SUBSTITUTE(A1,B1,"",1))

![enter image description here

Upvotes: 1

Related Questions