• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split Sentence into two parts

V

VDS

Member
Dear All,

In Cell A1 I have the the text data "ABC to XYZ", in A3, "London to New York" like that. Total No of rows say 50. What I want ABC to be extracted into Cell B2 and XYZ to be extracted into C2 and Like that. (Here to be removed). No of characters before and after "to" is not uniform. How it can be done with the formula.


VDS
 
Maybe:

for ABC =LEFT(A1,SEARCH(" ",A1,1)-1)

for XYZ = =RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(" ",A1)+1))
 
  • Like
Reactions: VDS
It's probably wiser to search for the entire " to " string in the source string - otherwise if your first city is New York for example then the formula above won't work because it is searching for a space and the city name contains a space.
Try
=LEFT(A1;SEARCH(" to ";A1)-1) for the first city, and
=MID(A1;SEARCH(" to ";A1)+4;1000) for the second

(note that in my Excel, function arguments are separated with a semi-colon)

- juanito
 
Hi,
Another way is.
Copy your data to range B2:C50. Select B2:B50 and hit Ctrl + H. In Find what: type " to*"(without quote). Leave 'Replace with' as it is. Now hit replace all button. You are left with first part of the text. Now select Range C2:C50 and Ctrl + H. If the find what type "*to "(without quote),leave replace with box blank.Hit Replace all button. You are left with 2nd part of text.
Regards
Rudra
 
  • Like
Reactions: VDS
@Debraj,

Wonderful work. So Nice. Instead of this | (Pipe). I can also use any signs. Is it right ? If it is so, what will be difference ?

VDS
 
Hi VDS

Yes.. you can use any other signs.. but be careful... Pipe is less used sign, where if you use % or *, there may be chances that sign is already there..
 
You must log in or register to reply here.
Back
Top