I need to be able to extract four numbers after a # in a text string.
For example:
Bought 3,544,000 shares of #0168 Air New Zealand
Bought 3,750,000 shares of #0198 Microsoft
Settled bought 1,970,000 shares of #0345 Google
Scrip deposit 77,144,000 shares of #0178 IBM
I need to be able to extract the 0198, 0345 and the 0178 into another cell.
If anyone can help it would be appreciated.
Also, if anyone can tell me how to extract the company name off the end of the string that would be great to.
Last edited by percyth1; 08-23-2012 at 11:09 PM.
Here's a way,
Assuming data as posted is running in A2 down
eg in B2: =MID(A2,SEARCH("#",A2)+1,4)
Copy down
Last edited by Max, Singapore; 08-24-2012 at 02:06 AM.
Wow, thanks that works!
Hi
if you select the range with your text, this macro will copy the number of shares into the cell to the right, and the name of the company in the cell to the right of that:
Please Login or Register to view this content.
That also works nicely, thanks.
***JUST if ever you have more than 4 numbers (static numbers)
Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Glad to hear. You could express your satisfaction & happiness by clicking the little star at the bottom left of my response, cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks