Parsing Text When Only One Character Can Be Used To Search

Hello:
I've spent a couple of hours looking on this site, and have found some 
excellent new tips, but still need to ask for assistance.  I have a series of 
text rows containing one string that I need to parse.  There are no 
characters I can use as delimeters (inconsistent), and there is no consistent 
use of text qualifiers; it's in effect, free form text.  Here's a sample 
record
------------------------------------------------------------------------------------------
FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
What I need to pull from this string is
------------------------------------------------------------------------------------------
per_requisitions_v@appslink

AND

per_vacancies@appslin
------------------------------------------------------------------------------------------
The only character I can use is the @ symbol; it's what tells me that a 
record should be reviewed and contains the desired data.  But I need the text 
before and after this character.  Standard worksheet functions SEARCH and 
FIND do not accept a negative start_num value; they won't search and retreive 
in reverse.  I would greatly appreciate suggestions for pulling this data; 
there are thousands of rows, even when I isolate those containing the desired 
data.  Some of the rows have multiple occurences, and all have much 
extraneous data I want to remove.

Thank you for your help,

LHSallwasser
0
2/9/2006 11:28:27 PM
excel 39879 articles. 2 followers. Follow

8 Replies
866 Views

Similar Articles

[PageSpeed] 2

Hi!

I notice that each substring starts with "per". Is that a constant?

There's also "req2" and "vac2" after each substring. Is that a constant?

What about the "appslink" after each <at> sign. Are those constants?

How about posting several samples so we can see if we find a pattern.

Biff

"LHSallwasser" <LHSallwasser@discussions.microsoft.com> wrote in message 
news:E80A47E8-185F-488F-BF9D-526CAA95D0C9@microsoft.com...
> Hello:
> I've spent a couple of hours looking on this site, and have found some
> excellent new tips, but still need to ask for assistance.  I have a series 
> of
> text rows containing one string that I need to parse.  There are no
> characters I can use as delimeters (inconsistent), and there is no 
> consistent
> use of text qualifiers; it's in effect, free form text.  Here's a sample
> record:
> ------------------------------------------------------------------------------------------
> FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> What I need to pull from this string is:
> ------------------------------------------------------------------------------------------
> per_requisitions_v@appslink
>
> AND
>
> per_vacancies@appslink
> ------------------------------------------------------------------------------------------
> The only character I can use is the @ symbol; it's what tells me that a
> record should be reviewed and contains the desired data.  But I need the 
> text
> before and after this character.  Standard worksheet functions SEARCH and
> FIND do not accept a negative start_num value; they won't search and 
> retreive
> in reverse.  I would greatly appreciate suggestions for pulling this data;
> there are thousands of rows, even when I isolate those containing the 
> desired
> data.  Some of the rows have multiple occurences, and all have much
> extraneous data I want to remove.
>
> Thank you for your help,
>
> LHSallwasser 


0
biffinpitt (3171)
2/10/2006 6:41:47 AM
One way is to use a UserDefinedFunction.

If you want to try...

Option Explicit
Function ExtractAddr(iStr As String) As Variant

    Dim iCtr As Long
    Dim aCtr As Long
    Dim mySplit As Variant
    Dim myArr() As Variant
    
    With Application
        iStr = .Trim(.Substitute(iStr, ",", " "))
    End With
    mySplit = Split97(iStr, " ")
    aCtr = 0
    For iCtr = LBound(mySplit) To UBound(mySplit)
        If InStr(1, mySplit(iCtr), "@", vbTextCompare) Then
            aCtr = aCtr + 1
            ReDim Preserve myArr(1 To aCtr)
            myArr(aCtr) = mySplit(iCtr)
        End If
    Next iCtr
            
    If aCtr = 0 Then
        ExtractAddr = ""
    ElseIf Application.Caller.Rows.Count = 1 Then
        ExtractAddr = myArr
    Else
        ExtractAddr = Application.Transpose(myArr)
    End If
    
End Function
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
  Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

This goes in a general module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Then you can use it like:

Selecting as many cells (in a row or in a column) that you think you need:

then type:
=extractaddr(A1)

but hit ctrl-shift-enter to fill that selection with this array formula.

If you selected too many cells, you'll get #n/a's back at the end.

LHSallwasser wrote:
> 
> Hello:
> I've spent a couple of hours looking on this site, and have found some
> excellent new tips, but still need to ask for assistance.  I have a series of
> text rows containing one string that I need to parse.  There are no
> characters I can use as delimeters (inconsistent), and there is no consistent
> use of text qualifiers; it's in effect, free form text.  Here's a sample
> record:
> ------------------------------------------------------------------------------------------
> FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> What I need to pull from this string is:
> ------------------------------------------------------------------------------------------
> per_requisitions_v@appslink
> 
> AND
> 
> per_vacancies@appslink
> ------------------------------------------------------------------------------------------
> The only character I can use is the @ symbol; it's what tells me that a
> record should be reviewed and contains the desired data.  But I need the text
> before and after this character.  Standard worksheet functions SEARCH and
> FIND do not accept a negative start_num value; they won't search and retreive
> in reverse.  I would greatly appreciate suggestions for pulling this data;
> there are thousands of rows, even when I isolate those containing the desired
> data.  Some of the rows have multiple occurences, and all have much
> extraneous data I want to remove.
> 
> Thank you for your help,
> 
> LHSallwasser

-- 

Dave Peterson
0
petersod (12004)
2/10/2006 12:46:04 PM
Of course as Biff suggests, it would be easier with more data to consider, 
but try these in the meantime.........

=MID(A1,FIND("per_requisitions_v@",A1,1),LEN(A1)-FIND("req2",A1,1)-6)

=MID(A1,FIND("req2",A1,1)+6,LEN(A1)-FIND("req2",A1,1)-11)

Vaya con Dios,
Chuck, CABGx3


"LHSallwasser" wrote:

> Hello:
> I've spent a couple of hours looking on this site, and have found some 
> excellent new tips, but still need to ask for assistance.  I have a series of 
> text rows containing one string that I need to parse.  There are no 
> characters I can use as delimeters (inconsistent), and there is no consistent 
> use of text qualifiers; it's in effect, free form text.  Here's a sample 
> record:
> ------------------------------------------------------------------------------------------
> FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> What I need to pull from this string is:
> ------------------------------------------------------------------------------------------
> per_requisitions_v@appslink
> 
> AND
> 
> per_vacancies@appslink
> ------------------------------------------------------------------------------------------
> The only character I can use is the @ symbol; it's what tells me that a 
> record should be reviewed and contains the desired data.  But I need the text 
> before and after this character.  Standard worksheet functions SEARCH and 
> FIND do not accept a negative start_num value; they won't search and retreive 
> in reverse.  I would greatly appreciate suggestions for pulling this data; 
> there are thousands of rows, even when I isolate those containing the desired 
> data.  Some of the rows have multiple occurences, and all have much 
> extraneous data I want to remove.
> 
> Thank you for your help,
> 
> LHSallwasser
0
CLR (807)
2/10/2006 12:48:27 PM
Hello:
Thank you Biff and Chuck for your replies; the problem is that there is no 
pattern before the @ symbol in the data, so I could not use your suggestions 
or the seeded Excel functions.

Dave - thank you so much for the user defined function!  It's exactly what I 
needed and works to extract the "before@after" string from the mess of 
PL/SQL.  I've been reading through David McRitchie's Excel page, and used his 
CountOccurrences function.  It's great that you and other gurus take the time 
to help the not-so-gifted rest of us.  

Best regards,

LHollister Sallwasser



"Dave Peterson" wrote:

> One way is to use a UserDefinedFunction.
> 
> If you want to try...
> 
> Option Explicit
> Function ExtractAddr(iStr As String) As Variant
> 
>     Dim iCtr As Long
>     Dim aCtr As Long
>     Dim mySplit As Variant
>     Dim myArr() As Variant
>     
>     With Application
>         iStr = .Trim(.Substitute(iStr, ",", " "))
>     End With
>     mySplit = Split97(iStr, " ")
>     aCtr = 0
>     For iCtr = LBound(mySplit) To UBound(mySplit)
>         If InStr(1, mySplit(iCtr), "@", vbTextCompare) Then
>             aCtr = aCtr + 1
>             ReDim Preserve myArr(1 To aCtr)
>             myArr(aCtr) = mySplit(iCtr)
>         End If
>     Next iCtr
>             
>     If aCtr = 0 Then
>         ExtractAddr = ""
>     ElseIf Application.Caller.Rows.Count = 1 Then
>         ExtractAddr = myArr
>     Else
>         ExtractAddr = Application.Transpose(myArr)
>     End If
>     
> End Function
> Function Split97(sStr As String, sdelim As String) As Variant
> 'from Tom Ogilvy
> Split97 = Evaluate("{""" & _
>   Application.Substitute(sStr, sdelim, """,""") & """}")
> End Function
> 
> This goes in a general module.
> 
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> 
> 
> Then you can use it like:
> 
> Selecting as many cells (in a row or in a column) that you think you need:
> 
> then type:
> =extractaddr(A1)
> 
> but hit ctrl-shift-enter to fill that selection with this array formula.
> 
> If you selected too many cells, you'll get #n/a's back at the end.
> 
> LHSallwasser wrote:
> > 
> > Hello:
> > I've spent a couple of hours looking on this site, and have found some
> > excellent new tips, but still need to ask for assistance.  I have a series of
> > text rows containing one string that I need to parse.  There are no
> > characters I can use as delimeters (inconsistent), and there is no consistent
> > use of text qualifiers; it's in effect, free form text.  Here's a sample
> > record:
> > ------------------------------------------------------------------------------------------
> > FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> > What I need to pull from this string is:
> > ------------------------------------------------------------------------------------------
> > per_requisitions_v@appslink
> > 
> > AND
> > 
> > per_vacancies@appslink
> > ------------------------------------------------------------------------------------------
> > The only character I can use is the @ symbol; it's what tells me that a
> > record should be reviewed and contains the desired data.  But I need the text
> > before and after this character.  Standard worksheet functions SEARCH and
> > FIND do not accept a negative start_num value; they won't search and retreive
> > in reverse.  I would greatly appreciate suggestions for pulling this data;
> > there are thousands of rows, even when I isolate those containing the desired
> > data.  Some of the rows have multiple occurences, and all have much
> > extraneous data I want to remove.
> > 
> > Thank you for your help,
> > 
> > LHSallwasser
> 
> -- 
> 
> Dave Peterson
> 
0
2/10/2006 2:58:28 PM
On Thu, 9 Feb 2006 15:28:27 -0800, LHSallwasser
<LHSallwasser@discussions.microsoft.com> wrote:

>Hello:
>I've spent a couple of hours looking on this site, and have found some 
>excellent new tips, but still need to ask for assistance.  I have a series of 
>text rows containing one string that I need to parse.  There are no 
>characters I can use as delimeters (inconsistent), and there is no consistent 
>use of text qualifiers; it's in effect, free form text.  Here's a sample 
>record:
>------------------------------------------------------------------------------------------
>FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
>What I need to pull from this string is:
>------------------------------------------------------------------------------------------
>per_requisitions_v@appslink
>
>AND
>
>per_vacancies@appslink
>------------------------------------------------------------------------------------------
>The only character I can use is the @ symbol; it's what tells me that a 
>record should be reviewed and contains the desired data.  But I need the text 
>before and after this character.  Standard worksheet functions SEARCH and 
>FIND do not accept a negative start_num value; they won't search and retreive 
>in reverse.  I would greatly appreciate suggestions for pulling this data; 
>there are thousands of rows, even when I isolate those containing the desired 
>data.  Some of the rows have multiple occurences, and all have much 
>extraneous data I want to remove.
>
>Thank you for your help,
>
>LHSallwasser

Simple with regular expressions.

What you apparently want is the word (with underscores) preceding the '@' and
the word following it.

If you download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

then the expression:

=REGEX.MID(A1,"\S+@\w+",1)

will pull out the first

and

=REGEX.MID(A1,"\S+@\w+",2)

will pull out the second.

Note that the @ character is causing the regex in the above formulas to be
underlined.  That should not really happen in Excel.
--ron
0
ronrosenfeld (3122)
2/10/2006 8:20:38 PM
Hello

i have a question along the same lines and so i was hoping that posting in 
this thread would be ok.

i have a refreshable web query where there are lines of text like this:

Hitachi Deskstar 180GXP 80GB IDE ATA100 7200RPM 2MB 8.8MS Hard Drive 3 Year 
MFR Warranty 

what i would like to know how to do is get the 80GB out of that test and all 
the GB amounts out of the other lines with a formula.

what i am trying to do is create a way for me to see what drive is the best 
for the price and i need to get to a numerical value to calculate on.

so first i would have to pull the text for 80GB. then turn it in to a 
numerical value. if that takes 2 columns on my sheet that is ok, but the 
first part absolutely stumps me.

if anyone can help it would be appreciated.

"LHSallwasser" wrote:

> Hello:
> I've spent a couple of hours looking on this site, and have found some 
> excellent new tips, but still need to ask for assistance.  I have a series of 
> text rows containing one string that I need to parse.  There are no 
> characters I can use as delimeters (inconsistent), and there is no consistent 
> use of text qualifiers; it's in effect, free form text.  Here's a sample 
> record:
> ------------------------------------------------------------------------------------------
> FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> What I need to pull from this string is:
> ------------------------------------------------------------------------------------------
> per_requisitions_v@appslink
> 
> AND
> 
> per_vacancies@appslink
> ------------------------------------------------------------------------------------------
> The only character I can use is the @ symbol; it's what tells me that a 
> record should be reviewed and contains the desired data.  But I need the text 
> before and after this character.  Standard worksheet functions SEARCH and 
> FIND do not accept a negative start_num value; they won't search and retreive 
> in reverse.  I would greatly appreciate suggestions for pulling this data; 
> there are thousands of rows, even when I isolate those containing the desired 
> data.  Some of the rows have multiple occurences, and all have much 
> extraneous data I want to remove.
> 
> Thank you for your help,
> 
> LHSallwasser
0
2/26/2006 5:06:26 PM
This is what I came up with...

For text in A1
B1: 
=RIGHT(LEFT(A1,FIND("GB",A1)+1),LEN(LEFT(A1,FIND("GB",A1)+1))-LOOKUP(LEN(LEFT(A1,FIND("GB",A1)+1)),FIND(" 
",LEFT(A1,FIND("GB",A1)+1),ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(LEFT(A1,FIND("GB",A1)+1)),1)))))

Using your sample text 
A1: Hitachi Deskstar 180GXP 80GB IDE ATA100 7200RPM 2MB 8.8MS Hard Drive 3 
Year MFR Warranty

That formula returns "80GB" in B1

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DawnTreader" wrote:

> Hello
> 
> i have a question along the same lines and so i was hoping that posting in 
> this thread would be ok.
> 
> i have a refreshable web query where there are lines of text like this:
> 
> Hitachi Deskstar 180GXP 80GB IDE ATA100 7200RPM 2MB 8.8MS Hard Drive 3 Year 
> MFR Warranty 
> 
> what i would like to know how to do is get the 80GB out of that test and all 
> the GB amounts out of the other lines with a formula.
> 
> what i am trying to do is create a way for me to see what drive is the best 
> for the price and i need to get to a numerical value to calculate on.
> 
> so first i would have to pull the text for 80GB. then turn it in to a 
> numerical value. if that takes 2 columns on my sheet that is ok, but the 
> first part absolutely stumps me.
> 
> if anyone can help it would be appreciated.
> 
> "LHSallwasser" wrote:
> 
> > Hello:
> > I've spent a couple of hours looking on this site, and have found some 
> > excellent new tips, but still need to ask for assistance.  I have a series of 
> > text rows containing one string that I need to parse.  There are no 
> > characters I can use as delimeters (inconsistent), and there is no consistent 
> > use of text qualifiers; it's in effect, free form text.  Here's a sample 
> > record:
> > ------------------------------------------------------------------------------------------
> > FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> > What I need to pull from this string is:
> > ------------------------------------------------------------------------------------------
> > per_requisitions_v@appslink
> > 
> > AND
> > 
> > per_vacancies@appslink
> > ------------------------------------------------------------------------------------------
> > The only character I can use is the @ symbol; it's what tells me that a 
> > record should be reviewed and contains the desired data.  But I need the text 
> > before and after this character.  Standard worksheet functions SEARCH and 
> > FIND do not accept a negative start_num value; they won't search and retreive 
> > in reverse.  I would greatly appreciate suggestions for pulling this data; 
> > there are thousands of rows, even when I isolate those containing the desired 
> > data.  Some of the rows have multiple occurences, and all have much 
> > extraneous data I want to remove.
> > 
> > Thank you for your help,
> > 
> > LHSallwasser
0
2/26/2006 5:37:57 PM
Hello

WOW. thanks. it works great. i am going to have to study it a little bit to 
fully understand it, but it works great.

"Ron Coderre" wrote:

> This is what I came up with...
> 
> For text in A1
> B1: 
> =RIGHT(LEFT(A1,FIND("GB",A1)+1),LEN(LEFT(A1,FIND("GB",A1)+1))-LOOKUP(LEN(LEFT(A1,FIND("GB",A1)+1)),FIND(" 
> ",LEFT(A1,FIND("GB",A1)+1),ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(LEFT(A1,FIND("GB",A1)+1)),1)))))
> 
> Using your sample text 
> A1: Hitachi Deskstar 180GXP 80GB IDE ATA100 7200RPM 2MB 8.8MS Hard Drive 3 
> Year MFR Warranty
> 
> That formula returns "80GB" in B1
> 
> Does that help?
> 
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP-Pro
> 
> 
> "DawnTreader" wrote:
> 
> > Hello
> > 
> > i have a question along the same lines and so i was hoping that posting in 
> > this thread would be ok.
> > 
> > i have a refreshable web query where there are lines of text like this:
> > 
> > Hitachi Deskstar 180GXP 80GB IDE ATA100 7200RPM 2MB 8.8MS Hard Drive 3 Year 
> > MFR Warranty 
> > 
> > what i would like to know how to do is get the 80GB out of that test and all 
> > the GB amounts out of the other lines with a formula.
> > 
> > what i am trying to do is create a way for me to see what drive is the best 
> > for the price and i need to get to a numerical value to calculate on.
> > 
> > so first i would have to pull the text for 80GB. then turn it in to a 
> > numerical value. if that takes 2 columns on my sheet that is ok, but the 
> > first part absolutely stumps me.
> > 
> > if anyone can help it would be appreciated.
> > 
> > "LHSallwasser" wrote:
> > 
> > > Hello:
> > > I've spent a couple of hours looking on this site, and have found some 
> > > excellent new tips, but still need to ask for assistance.  I have a series of 
> > > text rows containing one string that I need to parse.  There are no 
> > > characters I can use as delimeters (inconsistent), and there is no consistent 
> > > use of text qualifiers; it's in effect, free form text.  Here's a sample 
> > > record:
> > > ------------------------------------------------------------------------------------------
> > > FROM per_requisitions_v@appslink req2, per_vacancies@appslink vac2,
> > > What I need to pull from this string is:
> > > ------------------------------------------------------------------------------------------
> > > per_requisitions_v@appslink
> > > 
> > > AND
> > > 
> > > per_vacancies@appslink
> > > ------------------------------------------------------------------------------------------
> > > The only character I can use is the @ symbol; it's what tells me that a 
> > > record should be reviewed and contains the desired data.  But I need the text 
> > > before and after this character.  Standard worksheet functions SEARCH and 
> > > FIND do not accept a negative start_num value; they won't search and retreive 
> > > in reverse.  I would greatly appreciate suggestions for pulling this data; 
> > > there are thousands of rows, even when I isolate those containing the desired 
> > > data.  Some of the rows have multiple occurences, and all have much 
> > > extraneous data I want to remove.
> > > 
> > > Thank you for your help,
> > > 
> > > LHSallwasser
0
2/27/2006 2:09:27 AM
Reply:

Similar Artilces:

Parsing Question
I've done parsing where you have one delimiter such as a comma or space. What if you have more than one? Example data: sStr(0) = "3/9" sStr(1) = "3/11 3/15-" sStr(2) = "3/8+ 3/12" sStr(3) = "3/9 3/15" sStr(4) = "3/8- 3/10+ 3/15" The strings above are Month/Day with some having an additional - or + character next to it. These dates are extracted from Excel cells as strings. I need to break those strings down into individual dates. So for sStr(4), I need to parse out "3/8", "3/10" and "3/15&...

Text box in chart disappears but reappears when in spreadsheet
Has anyone any idea how to prevent a text box that's in a graph from disappearing when the graph is selected, but reappearing when cursor is back in the spreadsheet? This never used to happen before, but only recently has begun occuring with our graphs. Thanks! Hi, That behavior would suggest the textbox is not embedded in the chart but just placed over it. Can you move the textbox away from the chart? If yes then delete the textbox and then select the chart and paste. Cheers Andy Eggcel wrote: > Has anyone any idea how to prevent a text box that's in a graph from > di...

returning a text value
I think this is simple but I am confused with it. I am creating a softball roster. Column a is the positions, Column b is player for inning 1. C is inning 2 and so on. So with my current grid I can see who is in which position by inning. My second grid will show players name followed by position being played. Column F lists all the players. I need a function that will search column b for matches to column F and insert the position (column a) in column G. Check Help for Vlookup. Regards, Fred "Tim" <Tim@discussions.microsoft.com> wrote in message news:133...

Ordering xml using xsl in c#
Hi, I have a xml dataset I'd like to order, and save in that order. I've written an xsl to do the transformation, but because the xml file has a xsd namespace, it doesn't work. A cutdown version of the xml file looks like this: <?xml version="1.0" standalone="yes"?> <hsPrinters xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance" xmlns="http://tempuri.org/printer.xsd"> <city> <code>LN</code> <name>London</name> <comment>Herbert Smith London Offices</comment> </ci...

How to total three separate reports into one report:
I have three separate reports that are working great! Management would like to see the total lines of each report on one page without the detail that feeds to the summary of each column. How do I compile the totals of these three separate reports into one page w/just a title and total of the categories that they would like to see from each report? Thank you. Example: Report Name # # # Category 1 4 5 6 Category 2 2 8 1 Category 3 9 7 4 Evelyn Williams wrote: >I have three separate reports that are working great! Management would like >to see the tota...

Text size #4
I'm sure this is probably very easy! but I can't find any way to increase the size of an email in the viewing panel of Outlook, is there a way to do this and possibly to add a button to the toolbar to enable easy resizing of text? Thanks "Harry Limey" <harrylimey(at)Lycos.co.uk> wrote in message news:%23m1mP7xBIHA.1188@TK2MSFTNGP04.phx.gbl... > I'm sure this is probably very easy! but I can't find any way to increase > the size of an email in the viewing panel of Outlook, is there a way to do > this and possibly to add a button to the toolbar...

i have microsoft works spreadsheet not excell, can i upgrade to ex
Can I upgarde or do i need to buy the full version? There's upgrade eligibility information on the Microsoft site: http://www.microsoft.com/office/editions/howtobuy/standard.mspx Anne wrote: > Can I upgarde or do i need to buy the full version? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Text list to graph
Hi: I have a column listing car makes in random order with duplicates: Mazda Honda Honda Toyota Chevy Mazda... etc. QUESTION: How can I create a chart from this list? I've tried sorting, then sub-totaling. Am I on the right track? Thanks, Mark Flynn You can summarize the data with a Pivot Table, and create a chart from that. Add a heading to the list, e.g. Cars Select a cell in the table. Choose Data>PivotTable and PivotChart Report Select PivotChart report (with PivotTable report), click Next Select the Data range, click Next Click Layout Drag the Cars button to the Row area Dr...

Generating a table of figures and table of tables using 'insert caption'
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi, <br><br>I have set up a template in Word 2008 for some scientists to use. They want to insert captions using the 'Insert', 'Caption...' option for tables and figures, rather than using the style sheet 'figure caption' and 'table caption' that I set up so that the captions can be picked up by the cross-reference function. <br><br>Is there a way for my figure caption and table caption styles to be picked up by the cross-reference function? <br><br>Al...

Could not open one or more attachments #4
User has Outlook 2000 on a WinXP Pro PC and gets this error when opening attachments on some emails (not all). Tried reloading Outlook, but that made no difference. Any ideas? Thanks, John AnytimeAnywhere <gm79ta@yahoo.com> wrote: > User has Outlook 2000 on a WinXP Pro PC and gets this error when > opening attachments on some emails (not all). Post the complete and exact text of the error. -- Brian Tillman Could not open one or more attachments. "Brian Tillman" <tillman1952@yahoo.com> wrote in message news:OSMv3wFlFHA.2916@TK2MSFTNGP14.phx.gbl... &g...

how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible?
how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible? "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:%23g11DbDOGHA.2176@TK2MSFTNGP10.phx.gbl... > how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it > possible? No -- the xml declaration is not represented in the XML Infoset. Cheers, Dimitre Novatchev ...

From CRM call, an SSRS report that uses a stored Procedure.
All of my SSRS reports use store procedures. We recently started using MSCRM and I read that we can use tokens (CRMAF_) to pass paramters from CRm to SSRS. I have seen canned reports that use a stored procedure instead of a text query, but I can't create a new report that works. How do I set up the report so the results will be only for the Account the user is currently viewing? Thanks in advance, Michael The SDK has a reporting section with some info on this: http://msdn2.microsoft.com/en-us/library/aa682791.aspx -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "...

Converting capitalized text to lower case
Is it possible (if so, how?) to convert capitalized text to lowercase within cells - - when they were typed in ALL CAPS mode at the onset? If you've got a solution to this one, I'll really be impressed! =) Evie VBA? For Each cell In Selection cell.Value = LCase(cell.Value) Next cell -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Evie" <rossie@tcstoner.com> wrote in message news:28a8201c46512$e21f5f20$a601280a@phx.gbl... > Is it possible (if so, how?...

Use Outlook 2000 to retrieve Hotmail email?
Can someone tell me how to Use Outlook 2000 to retrieve Hotmail email? It keeps asking for a POP3 account info, when hotmail uses HTTP instead, very frustating. Thanks I don't think you can do this - you need to use either Outlook Express or Outlook XP (2002). --Dean. "John Luton" <JL112055@yahoo.com> wrote in message news:39e901c355aa$b1e17550$a001280a@phx.gbl... > Can someone tell me how to Use Outlook 2000 to retrieve > Hotmail email? It keeps asking for a POP3 account info, > when hotmail uses HTTP instead, very frustating. Thanks ...

Compare one year to another, but just YTD
Let's see if I can explain this. I have two sheets, the first sheet is labeled 2005 and the second one is 2006. In A1 thru A12 on both sheets is Jan, Feb, Mar, etc. On 2005 B1 thru B12 we have monthly values. On 2006 B1 thru B7 (as we are just now going into Aug.) we have monthly values. In B13 on both sheets are totals. The problem is I can't compare on year to the next as the total on the 2005 sheet is for the full year. I'd like to add Label in A14 that says 2005 YTD and have a formula in B14 that looks at how many cells in the range B1 thru B12 on 2006 have values then...

Basic Search
Why do you have to close the search box before you can edit the cell that was found? That is a pain, because to continue searching you need to open the serch box again. Why does it not work like Word? Thanks, Shawn Hi depends on the Excel version. Excel 2003 allows this (don't know if this feature was available before this version) -- Regards Frank Kabel Frankfurt, Germany Shawn wrote: > Why do you have to close the search box before you can edit the cell > that was found? That is a pain, because to continue searching you > need to open the serch box again. Why does it no...

Text #2
I'm just trying to enter some text, maybe an equal sign "=", but it keeps taking it as a formula. What do I do? Hi enter '= -- Regards Frank Kabel Frankfurt, Germany "greekgodsurfer" <greekgodsurfer@discussions.microsoft.com> schrieb im Newsbeitrag news:66CA2349-A770-4F77-9256-0D9193D92E4C@microsoft.com... > I'm just trying to enter some text, maybe an equal sign "=", but it keeps > taking it as a formula. What do I do? Thanks, I looked all over for it in help and couldn't find anything! greekgodsurfer "Frank Kabel"...

How I can specify the length of a text or binary column to larger value?
When the using Microsoft Excel How I can specify the length of a text or binary column to a larger value? otherwise, if the length of a text or binary column not specified (or is specified as 0), the column length will be set to 255. Hi not really sure what you're trying to achieve? -- Regards Frank Kabel Frankfurt, Germany "Yue Wu" <wuyue15@hotmail.com> schrieb im Newsbeitrag news:433001c4a48d$76b0aa90$a601280a@phx.gbl... > When the using Microsoft Excel How I can specify the > length of a text or binary column to a larger value? > otherwise, if the lengt...

Selecting multiple criteria in a report in Acc 2007 using a form
How can I select multiple criteria using a form to generate a report in Access 2007. -- Lady_Dee On Wed, 10 Mar 2010 17:38:04 -0800, Lady_Dee <LadyDee@discussions.microsoft.com> wrote: >How can I select multiple criteria using a form to generate a report in >Access 2007. By putting multiple instances of =[Forms]![NameOfForm]![NameOfControl] or other appropriate expressions on the Criteria line of a query, and use that query as the Recordsource for the report. For a more detailed answer please post a more detailed question. -- John W. Vins...

Maintain PO Line item number sequence when one is deleted.
Once a PO is printed (released), if a line item is deleted, all subsequent line item numbers are resequenced. I would prefer they are not resequenced to maintain accurate history and to all me to tell the vendor that line item 13 was deleted. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Parsing cells
Hi Folks, I have data in cells which is OCR`d from my phone bill, and some of the info is corrupt. Where I should be getting eg. 17 Nov 14:49 for date/time I am getting O17 Nov 14:49 or C17Nov 14:49. The reason is that there are unreadable icon characters to signify day or night. Questions: 1)How do I remove the faulty characters......they are always the first digit in the date if they are there. 2)How can I seperate the date and time into two adjasent colums of cells rather than their current format in one cell. Any and all help greatly appreciated. John Hi John You ca...

text display in MDI
Hello Friends Iam niranjan Developing MDI Application in vc++6.0 when iam displaying text in windows iam using OnDraw and OnChar functions. in OnChar function iam incrementing the x,y positions accoording to Key strokes. and making the string buffer to empty in "Enter" Key stroke in OnDraw function by using pDC->TextOut(x,y,str) iam displaying the text here iam not getting the proper data.... my problems are.... 1. after giving ther enter key curson going to next line but previous lines data in erasing , after ever enter key.. if any one knows solution plz help me to get th...

Text to Columns #2
Having used the "Text to Columns" tool once on a worksheet (to separate by spaces), I find I can't switch it off. Every subsequent time I try to copy and paste some other text from a single cell, for some completely different reason, the pasted data gets spread over many columns if there are any spaces in the text. How can I switch off "Text to Columns" after using it? This surely must be a bug, it's so unreasonable! Tod Excel likes to help by remembering your last settings. This can be really handy if you're pasting similar stuff lots of times. You ...

Parse Nested Elements to Single DataGrid
I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML document into a text document with a single row for each parent node (that has all of the values from all of the child nodes for that row) The DataView within VS 2005 IDE displays my 15 or so child tables - and knows that some parent rows don't have child rows in every relation - but trying to get all the child tables to go with the correct parent row programmatically is killin...

search filter cannot be recognized
Can you tell me where I would make the change to the DN as described below? I don't see an open parenthesis anywhere, how do I find this? Thanks Hi, The problem is that in the distinguished name of the database there is a single parenthesis without its match. Examples of this would include: cn=Mailbox Store 1 (SG1,cn=First Storage Group,.... cn=Mailbox Store 1 (SG2),cn=Second Storage Group (TestServer,cn=... If you add the closing parenthesis and force replication of the Active Directory the "Add Databases to Recover" dialog should work properly. The problem ...