4 New Messages
Digest #2726
Messages
Wed Aug 7, 2013 7:44 am (PDT) . Posted by:
"Steven" stevenletzer
I use a GetTableCell formula to return a string of characters. The string is a description of a bond.
The string returned always follows the same format. First UPPER characters and PROPER characters. The string is never the same length and the first LOWER case character is never in the same position. The string looks like this:
"OAKLAND CNTY WATER BNDSMaterial events". In the sub-string BNDSMaterial, there is no space after the BNDS and before the
"Material";. In this example, "Material";, starts the sub-string, but can be any of hundreds of different words. The same applies to the UPPER case letters.
A non-printable line break may exist, but that does not show in the string.
The position of the first LOWER character is after "Material";. The remainder of the string is PROPER. The UPPER case string is the first part of the bond description. My goal is to split the cell into two cells. For example:
Cell A2 is the entire string, "OAKLAND CNTY WATER BNDSMaterial events"
Cell B2 is where I want the UPPER case characters.
Cell C2 is where I want to PROPER case characters.
I do know the character code for the beginning of the PROPER characters is greater than 95 The code for "a" is 97. So the start of the position PROPER case characters is character code 97-1; Material.
I have tried many different variations of the CHAR and CODE functions, all of which have failed.
For example in cell C2 I entered the formula: =find(CHAR(CODE()>90,A2,1)-1); 90 is the code for PROPER "A". The second character of the string is always LOWER case.
I have copied the string returned by the GetTableCell and pasted it as text, so the problem is NOT that I am trying to apply a text function to a formula.
Any ideas.
The string returned always follows the same format. First UPPER characters and PROPER characters. The string is never the same length and the first LOWER case character is never in the same position. The string looks like this:
"OAKLAND CNTY WATER BNDSMaterial events"
"Material"
A non-printable line break may exist, but that does not show in the string.
The position of the first LOWER character is after "Material"
Cell A2 is the entire string, "OAKLAND CNTY WATER BNDSMaterial events"
Cell B2 is where I want the UPPER case characters.
Cell C2 is where I want to PROPER case characters.
I do know the character code for the beginning of the PROPER characters is greater than 95 The code for "a" is 97. So the start of the position PROPER case characters is character code 97-1; Material.
I have tried many different variations of the CHAR and CODE functions, all of which have failed.
For example in cell C2 I entered the formula: =find(CHAR(CODE(
I have copied the string returned by the GetTableCell and pasted it as text, so the problem is NOT that I am trying to apply a text function to a formula.
Any ideas.
Wed Aug 7, 2013 8:12 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Try, array-entered:
B2:
=LEFT(A2,MATCH(1,(CODE(MID(A2,ROW($A$1:$A$255),1))<122)*(CODE(MID(A2,ROW($A$1:$A$255),1))>=97),FALSE)-2)
C2:
=MID(A2,MATCH(1,(CODE(MID(A2,ROW($A$1:$A$255),1))<122)*(CODE(MID(A2,ROW($A$1:$A$255),1))>=97),FALSE)-1,999)
...although I think you'd be better off just searching for the non-print
character.
On Wed, Aug 7, 2013 at 7:44 AM, Steven <stevenletzer@yahoo.com > wrote:
> I use a GetTableCell formula to return a string of characters. The string
> is a description of a bond.
>
> The string returned always follows the same format. First UPPER characters
> and PROPER characters. The string is never the same length and the first
> LOWER case character is never in the same position. The string looks like
> this:
>
> "OAKLAND CNTY WATER BNDSMaterial events". In the sub-string BNDSMaterial,
> there is no space after the BNDS and before the
> "Material";. In this example, "Material";, starts the sub-string, but can be
> any of hundreds of different words. The same applies to the UPPER case
> letters.
>
> A non-printable line break may exist, but that does not show in the string.
>
> The position of the first LOWER character is after "Material";. The
> remainder of the string is PROPER. The UPPER case string is the first part
> of the bond description. My goal is to split the cell into two cells. For
> example:
>
> Cell A2 is the entire string, "OAKLAND CNTY WATER BNDSMaterial events"
> Cell B2 is where I want the UPPER case characters.
> Cell C2 is where I want to PROPER case characters.
>
> I do know the character code for the beginning of the PROPER characters is
> greater than 95 The code for "a" is 97. So the start of the position PROPER
> case characters is character code 97-1; Material.
>
> I have tried many different variations of the CHAR and CODE functions, all
> of which have failed.
>
> For example in cell C2 I entered the formula:
> =find(CHAR(CODE()>90,A2,1)-1); 90 is the code for PROPER "A". The second
> character of the string is always LOWER case.
>
> I have copied the string returned by the GetTableCell and pasted it as
> text, so the problem is NOT that I am trying to apply a text function to a
> formula.
>
> Any ideas.
>
B2:
=LEFT(A2,MATCH(
C2:
=MID(A2,MATCH(
...although I think you'd be better off just searching for the non-print
character.
On Wed, Aug 7, 2013 at 7:44 AM, Steven <stevenletzer@
> I use a GetTableCell formula to return a string of characters. The string
> is a description of a bond.
>
> The string returned always follows the same format. First UPPER characters
> and PROPER characters. The string is never the same length and the first
> LOWER case character is never in the same position. The string looks like
> this:
>
> "OAKLAND CNTY WATER BNDSMaterial events"
> there is no space after the BNDS and before the
> "Material"
> any of hundreds of different words. The same applies to the UPPER case
> letters.
>
> A non-printable line break may exist, but that does not show in the string.
>
> The position of the first LOWER character is after "Material"
> remainder of the string is PROPER. The UPPER case string is the first part
> of the bond description. My goal is to split the cell into two cells. For
> example:
>
> Cell A2 is the entire string, "OAKLAND CNTY WATER BNDSMaterial events"
> Cell B2 is where I want the UPPER case characters.
> Cell C2 is where I want to PROPER case characters.
>
> I do know the character code for the beginning of the PROPER characters is
> greater than 95 The code for "a" is 97. So the start of the position PROPER
> case characters is character code 97-1; Material.
>
> I have tried many different variations of the CHAR and CODE functions, all
> of which have failed.
>
> For example in cell C2 I entered the formula:
> =find(CHAR(CODE(
> character of the string is always LOWER case.
>
> I have copied the string returned by the GetTableCell and pasted it as
> text, so the problem is NOT that I am trying to apply a text function to a
> formula.
>
> Any ideas.
>
Wed Aug 7, 2013 8:15 pm (PDT) . Posted by:
"justvotin1" justvotin1
Is there a way to capture that so I don't have to open a web dialog every time my browser gets cleared? I would like to be able to just transport the excel file around, and if it meant copying one or two small files to the same folder that would be great. I don't know the mechanism it uses to authenticate though and I'm having trouble finding any VBA info about it.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> When I first created it, I got "Error" as well.
>
> Then:
>
> 1. I opened up the EXCEL Web Query dialog,
> 2. I went to https://us.etrade.com/home
> 3. I entered a ticker symbol in the box
> 4. After the snapshot came up, I cancelled out of the EXCEL Web Query
> dialog.
> 5. I ran smfForceRecalculation to get a fresh copy of the web page
>
> I got back the ex-dividend date. I then changed the ticker symbol, and got
> the ex-dividend date for that ticker symbol.
>
> And, yes, if you haven't gone through the steps above, you will get the
> secure logon page.
>
> BTW -- I'm not an eTrade customer, so I can't log on.
>
> On Tue, Aug 6, 2013 at 12:47 PM, Kermit W. Prather
> <kermitp@...>wrote:
>
> >
> > I just tired the link and I get an Error.****
> >
> > ** **
> >
> > =RCHGetTableCell("
> > https://www.etrade.wallst.com/v1/stocks/snapshot/snapshot.asp?symbol=MMM ",
> > 1,">Ex-Dividend Date")****
> >
> > ** **
> >
> > When I post it into a browser it puts up the Etrade secure logon page. ***
> > *
> >
> > I logged onto Etrade and it still failed.
> >
>
--- In smf_addin@yahoogrou
>
> When I first created it, I got "Error" as well.
>
> Then:
>
> 1. I opened up the EXCEL Web Query dialog,
> 2. I went to https://us.etrade.
> 3. I entered a ticker symbol in the box
> 4. After the snapshot came up, I cancelled out of the EXCEL Web Query
> dialog.
> 5. I ran smfForceRecalculati
>
> I got back the ex-dividend date. I then changed the ticker symbol, and got
> the ex-dividend date for that ticker symbol.
>
> And, yes, if you haven't gone through the steps above, you will get the
> secure logon page.
>
> BTW -- I'm not an eTrade customer, so I can't log on.
>
> On Tue, Aug 6, 2013 at 12:47 PM, Kermit W. Prather
> <kermitp@
>
> >
> > I just tired the link and I get an Error.****
> >
> > ** **
> >
> > =RCHGetTableCell(
> > https://www.
> > 1,">
> >
> > ** **
> >
> > When I post it into a browser it puts up the Etrade secure logon page. ***
> > *
> >
> > I logged onto Etrade and it still failed.
> >
>
Wed Aug 7, 2013 9:48 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm sure there is a way to do it by interacting with the IE object.
However, I no longer use VBA to interact with the IE object, because I had
many such routines that broke going from EXCEL 2003 to EXCEL 2007. With so
many versions of IE and EXCEL out there, it just became a waste of time to
work on something that doesn't work with all the permutations of those
versions that can be out there. Especially since I'd have no way to test
anything -- I only have my version of IE and my version of EXCEL.
On Wed, Aug 7, 2013 at 8:15 PM, justvotin1 <justvotin1@yahoo.com > wrote:
> Is there a way to capture that so I don't have to open a web dialog every
> time my browser gets cleared? I would like to be able to just transport the
> excel file around, and if it meant copying one or two small files to the
> same folder that would be great. I don't know the mechanism it uses to
> authenticate though and I'm having trouble finding any VBA info about it.
>
However, I no longer use VBA to interact with the IE object, because I had
many such routines that broke going from EXCEL 2003 to EXCEL 2007. With so
many versions of IE and EXCEL out there, it just became a waste of time to
work on something that doesn't work with all the permutations of those
versions that can be out there. Especially since I'd have no way to test
anything -- I only have my version of IE and my version of EXCEL.
On Wed, Aug 7, 2013 at 8:15 PM, justvotin1 <justvotin1@yahoo.
> Is there a way to capture that so I don't have to open a web dialog every
> time my browser gets cleared? I would like to be able to just transport the
> excel file around, and if it meant copying one or two small files to the
> same folder that would be great. I don't know the mechanism it uses to
> authenticate though and I'm having trouble finding any VBA info about it.
>
Tidak ada komentar:
Posting Komentar