Spliting text

[Deepak Reddy]

Helo,

I have nearly 22,000 links on an excel sheet with different type of repeated characters in the text, so i want to split up the last CAS number in the brackets and separate them in an another column, i have tried it in text to column but due to repeated characters it is not happening for me. kindly help me with this issue, i would provide an example of texts here

Collagens, hydrolyzates, N-[3-(dodecyldimethylammonio)-2-hydroxypropyl], chlorides (CAS Number 132538-89-7)

Starch, dihydrogen phosphate, 2-(diethylamino)ethyl ether, sodium salt, hydrochloride (CAS Number 143734-28-5)

Fatty acids, soya, esters with polypropylene glycol ether with sorbitol (6:1) (CAS Number 127947-24-4)


So i have 22,000 links as such and i want only the ( CAS Number XXX-XX-X) in a seperate column, i would highly appreciate if somebody can help me ASAP. Thank you.

[Deepak Reddy]

I can upload the document so that you can take a look to get a better picture.

[elayaz]

Hi,

Select the column. Find and replace. In find type *(cas replace with empty.

Concatenate the output and with ( cas -- done

See attached file
-------

If my reply helped, Please add reputation (click on the star below the post)
If the reply solves your problem, Don't forget to mark the thread as "Solved" (Thread Tools->Mark thread as Solved)

[Deepak Reddy]

Sorry i could not check your reply yesterday, thanks for your help but i still could not figure it how to do it as i am completely new to excel and concatenate functions and all seems like rocket science to me. if there is any video kindly put it because i have to do for rest of the sheets.

[elayaz]

In the excel file i have uploaded earlier,

To get all the cas numbers, I have used find and replace to remove the text from beginning till (cas. . ( No other common thing in all the text)

Then I have added (cas again.


I don't have any link as of now. Just google it. See if this pages help.

http://office.microsoft.com/en-in/ex...001216390.aspx
http://office.microsoft.com/en-in/ex...005209020.aspx

[shuaibfarouk]

use
=RIGHT(A1,23)
and copy through the data.
download your file here with solution
https://www.dropbox.com/s/eyvmxkmbks...iological.xlsx