Hi Bharghav, You can extract the data by using formula which will do the same work which you are trying to derive by using the Text To Columns.
Change the cell reference A2 to your desired cell. This is a single cell
formula which will get rotated when dragging the formula to the right and
down.
=TRIM(MID(SUBSTITUTE("-" & $A2&
REPT("-",6),"-",REPT(CHAR(32),255)),COLUMNS($B$2:B$2)*255,255))
In the above formula I have mentioned the separator as "-" change it based
on your data separator.
I have attached my sample solution file for your easy reference.
Hope that helps!
---
*Sixthsense
**:) Man of Extreme & Innovative Thoughts :)*
On Sun, May 29, 2011 at 8:06 PM, Bharghav Ramdas <bhargha...@gmail.com>wrote:
> Hi ,
>
> Thanks for the response. This did solve part of my problem.However when I
> split the cell containing more than 255 characters using text to columns
> options. I see only the first 255 characters getting split, others are just
> ignored. Let me know how I can get away with this issue.
>
> Other members can also share your views.
>
> Rgds,
> Bharghav
>
> On Fri, May 27, 2011 at 12:08 PM, Sixthsense <sixthsense...@gmail.com>wrote:
>
>> Hi Bharghav,
>>
>> This problem will occur when you perform the sheet *Move or Copy *option.
>> My suggestion is just press Cntrl+A twice, which will select all data in
>> your active worksheet and press Cntrl+C (to copy) and in New Worksheet Press
>> Cntrl+A and Press Cntrl+V (to paste). In this case the data which is there
>> in the the cells more than 255 characters won\t get affected.
>>
>> Hope that helps!!!
>>
>> ---
>> *Sixthsense
>> **:) Man of Extreme & Innovative Thoughts :)
>>
>> *
>>
>> On Fri, May 27, 2011 at 9:50 AM, Bharghav Ramdas <bhargha...@gmail.com>wrote:
>>
>>> Hi All,
>>>
>>> Looking forward to your reponses.
>>>
>>> Rgds,
>>> Bharghav Ramdas
>>> On Tue, May 24, 2011 at 9:57 AM, Bharghav Ramdas
>>> <bhargha...@gmail.com>wrote:
>>>
>>>> Hi All,
>>>>
>>>> I am currently working on an excel (2003)sheet whose cell character
>>>> length is beyond 255. I am able to copy just 255 characters. The remaining
>>>> characters get truncated.Your help in this regard will be very much
>>>> appreciated.
>>>>
>>>>
>>>> Example
>>>> cell character length =300.
>>>> While pasting or doing any cell operations, I see only the first 255
>>>> characters considered.
>>>>
>>>>
>>>> Rgds,
>>>> Bharghav R
>>>>
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
TEXT TO COLUMNS USING FUNCTION.xls
Description: MS-Excel spreadsheet
