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....

 

[nightcrawler23]

Can you state what things are constant and what are variables for the data

 

[mohanyathish]

Constants are as follows:

UrlTrends Quick Summary
URL: XXXX
Google PageRank: XXXX
Alexa Traffic Rank: XXXX
Incoming Google Links: XXXX
Incoming Yahoo Links: XXXX
Incoming MSN Links: XXXX
Overall Incoming Links: XXXX
(Estimated XXXX unique links)
Outgoing Links: XXXX
DMOZ Listed: XXXX
Accurate as of: XXXX
Source: XXXX

The XXXX part are the variables

 

[nightcrawler23]

I have figured out a way. But i am unable to post my sheet as i m in office

let me try explaining

suppose you have your data in A1

A6 to A18 put a dummy table as you posted in the last post.

UrlTrends Quick Summary
URL: XXXX
Google PageRank: XXXX
Alexa Traffic Rank: XXXX
Incoming Google Links: XXXX
Incoming Yahoo Links: XXXX
Incoming MSN Links: XXXX
Overall Incoming Links: XXXX
(Estimated XXXX unique links)
Outgoing Links: XXXX
DMOZ Listed: XXXX
Accurate as of: XXXX
Source: XXXX

B6 to B18 formulas will be

B6 = FIND(LEFT(A6,4),$A$1,1)
B7 = FIND(LEFT(A7,4),$A$1,1)

and so on till B18

C6 to C18 Formulas are

C6 = MID($A$1,B6,(B7-B6))
C7 = MID($A$1,B7,(B8-B7))

Formula in B19 = Len($A$1) else it will give an error in B18

You can also combine these to columns to get a single formula in col B

 

[Peter_SSs]

Welcome to the MrExcel board!

You haven't said where your data is or where the results should go. The code below just acts on the active cell and puts the results in the cells immediately below the activecell.

Perhaps try this in a new sheet with the sample data you posted in cell A1. Select A1 then run the code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Split_Text()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><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><br>    <SPAN style="color:#00007F">Dim</SPAN> Starters<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>    a = <SPAN style="color:#00007F">LBound</SPAN>(Starters)<br>    b = <SPAN style="color:#00007F">UBound</SPAN>(Starters)<br>    s = ActiveCell.Value<br>    <SPAN style="color:#00007F">For</SPAN> i = a + 1 <SPAN style="color:#00007F">To</SPAN> b<br>        s = Replace(s, Starters(i), "|" & Starters(i), 1, 1, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    ActiveCell.Offset(1).Resize(b - a + 1).Value _<br>        = Application.Transpose(Split(s, "|"))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If this works but you need to act on multiple cells, then please advise the layout of the original data and the results.

 

[mohanyathish]

<!--[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]--> <table class="MsoNormalTable" style="width:433.5pt;margin-left:4.65pt;border-collapse:collapse;mso-padding-alt: 0in 5.4pt 0in 5.4pt" border="0" cellpadding="0" cellspacing="0" width="578"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]UrlTrends Quick Summary[/FONT]
</td> <td style="width:38.15pt;border:solid black 1.0pt; border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]1[/FONT]
</td> <td style="width:286.45pt;border:solid black 1.0pt; border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]UrlTrends Quick Summary [/FONT]
</td> </tr> <tr style="mso-yfti-irow:1;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]URL: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]25[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]URL: http://money.cnn.com [/FONT]
</td> </tr> <tr style="mso-yfti-irow:2;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Google PageRank: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]51[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Google PageRank: 8/10 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:3;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Alexa Traffic Rank: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]73[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Alexa Traffic Rank: 54 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:4;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Incoming Google Links: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]96[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="mso-yfti-irow:5;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Incoming Yahoo Links: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]96[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="mso-yfti-irow:6;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Incoming MSN Links: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]96[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Incoming Google Links: 1,650 Incoming Yahoo Links: 554 Incoming MSN Links: 0 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:7;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Overall Incoming Links: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]173[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Overall Incoming Links: 37,404 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:8;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot](Estimated XXXX unique links)[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]204[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot](Estimated 1,497 unique links) [/FONT]
</td> </tr> <tr style="mso-yfti-irow:9;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Outgoing Links: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]235[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Outgoing Links: 296 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:10;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]DMOZ Listed: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]255[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]DMOZ Listed: No [/FONT]
</td> </tr> <tr style="mso-yfti-irow:11;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Accurate as of: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]271[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Accurate as of: February 10th, 2011 [/FONT]
</td> </tr> <tr style="mso-yfti-irow:12;height:15.75pt"> <td style="width:108.9pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="145"> [FONT=&quot]Source: XXXX[/FONT]
</td> <td style="width:38.15pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]307[/FONT]
</td> <td style="width:286.45pt;border-top:none; border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="382"> [FONT=&quot]Source: http://www.urltrends.co[/FONT]
</td> </tr> <tr style="mso-yfti-irow:13;mso-yfti-lastrow:yes;height:15.75pt"> <td style="width:108.9pt;padding:0in 5.4pt 0in 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="145">
</td> <td style="width:38.15pt;border:solid black 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt" nowrap="nowrap" valign="bottom" width="51"> [FONT=&quot]338[/FONT]
</td> <td style="width:286.45pt;padding:0in 5.4pt 0in 5.4pt; height:15.75pt" nowrap="nowrap" valign="bottom" width="382">
</td> </tr> </tbody></table>


i am getting the above result....but the "[FONT=&quot]Incoming Google Links:" and "[/FONT][FONT=&quot]Incoming Yahoo Links:" is showing just blank...thanks for your effort and time....i will need a little more of your help....[/FONT]
[FONT=&quot]
[/FONT]

 

[nightcrawler23]

a6 to a18 put a dummy table as you posted in the last post.

REMOVED ALL XXXX

urltrends quick summary
url:
Google pagerank:
Alexa traffic rank:
Incoming google links:
Incoming yahoo links:
Incoming msn links:
Overall incoming links:
(estimated    'TAKE NOTE OF THIS LINE
outgoing links:
Dmoz listed:
Accurate as of:
Source:

b6 to b18 formulas will be

b6 = FIND(TRIM(A6),$A$1,1)
b7 = FIND(TRIM(A7),$A$1,1)

and so on till b18

c6 to c18 formulas are

c6 = mid($a$1,b6,(b7-b6))
c7 = mid($a$1,b7,(b8-b7))

formula in b19 = len($a$1) else it will give an error in b18

See changes in red. rest all are same.

 

[Peter_SSs]

If you wanted to pursue the formula approach, I would suggest the following changes:

1. In the column A table, remove all the XXXXs and anything that follows (and the space before the XXXXs). See my screen shot below.

2. Change the column B formulas from FIND to SEARCH. This will help if there is an upper/lower case change. Also stop just looking for the LEFT 4 characters - that's why some of your results were missing.

3. Add 1 to the LEN formula in B19 or the last character will be lost (as you see in your screen shot)

Excel Workbook
	A	B	C
6	UrlTrends Quick Summary	1	UrlTrends Quick Summary
7	URL:	25	URL: http://money.cnn.com
8	Google PageRank:	51	Google PageRank: 8/10
9	Alexa Traffic Rank:	73	Alexa Traffic Rank: 54
10	Incoming Google Links:	96	Incoming Google Links: 1,650
11	Incoming Yahoo Links:	125	Incoming Yahoo Links: 554
12	Incoming MSN Links:	151	Incoming MSN Links: 0
13	Overall Incoming Links:	173	Overall Incoming Links: 37,404
14	(Estimated	204	(Estimated 1,497 unique links)
15	Outgoing Links:	235	Outgoing Links: 296
16	DMOZ Listed:	255	DMOZ Listed: No
17	Accurate as of:	271	Accurate as of: February 10th, 2011
18	Source:	307	Source: http://www.urltrends.com
19		339
Split Text Formulas

 

[Marcelo Branco]

Hi Peter,

Nice code! I tested and it worked perfectly!!!

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.

 

[nightcrawler23]

.

3. Add 1 to the LEN formula in B19 or the last character will be lost (as you see in your screen shot)

Thnx peter. I missed that 1.