Macro / VBA separate texts and numbers

[mohanyathish]

hi,

I am using excel 2003 and 2007

I have data in the following format:

UrlTrends Quick Summary URL: http://money.cnn.com Google PageRank: 8/10 Alexa Traffic Rank: 54 Incoming Google Links: 1,650 Incoming Yahoo Links: 554 Incoming MSN Links: 0 Overall Incoming Links: 37,404 (Estimated 1,497 unique links) Outgoing Links: 296 DMOZ Listed: No Accurate as of: February 10th, 2011 Source: http://www.urltrends.com


I have all this data in a single cell.

I need that data to be in this format:


<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> UrlTrends Quick Summary
URL: http://money.cnn.com
Google PageRank: 8/10
Alexa Traffic Rank: 54
Incoming Google Links: 1,650
Incoming Yahoo Links: 554
Incoming MSN Links: 0
Overall Incoming Links: 37,404
(Estimated 1,497 unique links)
Outgoing Links: 296
DMOZ Listed: No
Accurate as of: February 10th, 2011
Source: http://www.urltrends.com




Please help


Thanks in advance....

 

[Peter_SSs]

I have a question about the REPLACE parameters you have used: is this correct?
http://www.techonthenet.com/access/functions/string/replace.php

I'm asking because it states:
MS Access Replace Function

Is it ok for Excel VBA too?

Tks in advance

M.

Marcelo

I am not an Access user but if you look in the vba Help for the Replace function you will see that the syntax has the same structure.

 

[mohanyathish]

thanks for your help....the data is in the cells in a single column.....i have checked the code..it is really cool.... i need a little more of your help....
each data can be separated in the same row.....
so that it would be easier for consolidation....

thanks once again....

 

[Marcelo Branco]

Marcelo

I am not an Access user but if you look in the vba Help for the Replace function you will see that the syntax has the same structure.

Hi Peter,

Sorry for the delay in replying (for some reason i didnt receive e-mail notifications about this thread - strange...the first time it happened to me)

Tks very much for answering.

M.

 

[Peter_SSs]

thanks for your help....the data is in the cells in a single column.....i have checked the code..it is really cool.... i need a little more of your help....
each data can be separated in the same row.....
so that it would be easier for consolidation....

thanks once again....

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Split_Text()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Starters<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A" <SPAN style="color:#007F00">'<- Original Data column</SPAN><br>    <br>    Starters = Array("UrlTrends Quick Summary", "URL:", "Google PageRank:", _<br>                    "Alexa Traffic Rank:", "Incoming Google Links:", _<br>                    "Incoming Yahoo Links:", "Incoming MSN Links:", _<br>                    "Overall Incoming Links:", "(Estimated", _<br>                    "Outgoing Links:", "DMOZ Listed:", _<br>                    "Accurate As of:", "Source:")<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    a = <SPAN style="color:#00007F">LBound</SPAN>(Starters)<br>    b = <SPAN style="color:#00007F">UBound</SPAN>(Starters)<br>    LR = Range(Col & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range(Col & 1).Resize(LR).Offset(, 1)<br>        .Value = .Offset(, -1).Value<br>        <SPAN style="color:#00007F">For</SPAN> i = a + 1 <SPAN style="color:#00007F">To</SPAN> b<br>            .Replace What:=" " & Starters(i), Replacement:="|" & Starters(i), _<br>                LookAt:=xlPart, MatchCase:=False, _<br>                SearchFormat:=False, ReplaceFormat:=False<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _<br>            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _<br>            Other:=True, OtherChar:="|"<br>        .Resize(, b - a + 1).EntireColumn.AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

However, I am also wondering if you really want/need those 'constant' texts repeated in every row. perhaps instead they could just be added as headings. The following does not do this job quite properly, but to see what I am talking about, try removing the results of the previous code from the sheet as well as the red bit of code below and running the procedure again.

.Replace What:=" " & Starters(i), Replacement:="|" & Starters(i), _
    LookAt:=xlPart, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

If that may be better, post back and I'll try to tidy it up a bit more (eg the (Estimated XXXX unique links) column would need to be improved).

Does the data already have a heading row?

 

[mohanyathish]

Hi Peter,

thanks for all the help and the suggestions......

the only problem as you already know is the "(Estimated XXXX unique links)"

i want that to be in the same format as in the main output...i.e., i need the entire phrase...

i guess this is sufficient as of now....

 

[Peter_SSs]

i guess this is sufficient as of now....

That doesn't sound all that enthusiastic.

In what way would you want the output changed compared to what the main code in my previous post produced.

 

[mohanyathish]

hi...

in fact...i am really very happy about the results....

 

[Peter_SSs]

hi...

in fact...i am really very happy about the results....

OK, that's good.

I thought you still weren't happy with ..

.. the only problem as you already know is the "(Estimated XXXX unique links)"

i want that to be in the same format as in the main output...i.e., i need the entire phrase...