How to sepate numbers and text

[ashokraju]

Hello


want to know how to separate text and numbers in single columns to two separate columns without any modifications in bold or italics in text


text content given below:


100 Vgl. World Trade Organization, Trade policy, 2006, S.viii: http://www.wto.org.

101 Vgl. World Trade Organization, Trade, 2006, S.11: http://www.wto.org.

102 Vgl. Brockhaus Enzyklopädie in 30 Bänden, Band 13, 2006, S.566.

103 Vgl. US-Departement of State, Background, 2009: http://www.state.gov.

104 Vgl. Botschaft der Republik Island - Berlin, Geschichte, 2008: http://www.botschaft-island.de.


Thanks

Ashok Raju

 

[Jolly]

Hi,


U can use "Text to Columns". Go to Data, click on Text to Colums..

 

[kaushik03]

Hi ashokraju,


You may want to see this article (written by Chandoo) to get a formula based solution for your problem:

http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/


Regards,

Kaushik

 

[ashokraju]

Hi Jolly


when we are using "text columns" we will get the separation of Numbers and text. I had already tried with "text columns" before post to you..But the prob is the text changed to normal text (bold, and italics are not comes in previous text)


Let me know any other solution for this...


Thanks

Ashok

 

[Luke M]

W/o getting into some pretty fancy VB, I don't think there's a way to split text up and keep the formatting intact.

 

[MickM]

Ashokraju

Luke M


I’m not sure that my contribution falls into the category of “some pretty fancy VB” but it does work


I have uploaded a file to https://dl.dropbox.com/u/95000482/Retain_Text_Formats_1a.xls


The file contains 2 sheets, one containing only 5 records (based on the original supplied data sample) and the other containing 5,000 (the same 5 records 1,000 times each!) which I used simply to test speed.


The code is extensively commented so I won’t go into detail here. It is not superfast - the test on 5,000 records takes just over a minute on my PC - but then it is checking the format of about 355,000 characters in the original records (5,000 records x approx. 70 characters per record) and amending the bold and italic properties of the corresponding character in the separated elements where the character IS bold or IS italic.


PS I’ve no idea why I felt bold enough with this particular problem to submit my first proper forum contribution, but it just pressed my “programming curiosity” button.