Help...Need to modify data within a column in a .csv file

I have been given a .csv file of several thousand rows, and I need to
change column A of each.  The brief cutout below shows column A of the
first two rows.  (I need to skip the first row, which is the headers.)
Kinda clumsy to have such a wide column, but the application this file
is to be fed after I change the contents requires all the data
separated by double-quotes to be in the one column.

Cell A2:
09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2
..
..
..
etc

I need to do two things:

1. Add a constant to the 7-digit number which always appears after the
first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes
1427386 (by adding constant of my choice in this case 10000).  I need
to apply this constant to column A of all rows.

2. Change the date which appears after the fifth double-quote to a new,
constant date.  09/30/05 would become 10/14/05 for all rows.  I don't
care what date is in that position, I just want to write over it with
the new one.  Note that the number after the third quote can be
variable in length (e.g., 307 vs 900000005).  I suppose part of the
solution could be to count (parse?) over five double-quotes to get to
this date.

All other data in the column remains the same.

Here's the after-image of the above example:

Cell A2:
09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2

TIA for any help you can provide.

Ed

0
ed0109 (5)
10/24/2005 7:00:32 PM
excel 39879 articles. 2 followers. Follow

11 Replies
727 Views

Similar Articles

[PageSpeed] 28

Assuming your data is all in Col A, that the data you have given is 
representative of all your data in as much as the constant that needs 
changing is in position 18 with a length of 7 digits, then assuming you have 
the constant you want in say A1, then in any helper column try the following 
and copy down as far as your data goes:-

=LEFT(A2,17)&TEXT((--MID(A2,18,7)+10000),"0000000")&MID(A2,25,LEN(A2))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------

<ed0109@volcanomail.com> wrote in message 
news:1130180432.814371.26130@f14g2000cwb.googlegroups.com...
>I have been given a .csv file of several thousand rows, and I need to
> change column A of each.  The brief cutout below shows column A of the
> first two rows.  (I need to skip the first row, which is the headers.)
> Kinda clumsy to have such a wide column, but the application this file
> is to be fed after I change the contents requires all the data
> separated by double-quotes to be in the one column.
>
> Cell A2:
> 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1
> Cell A3:
> 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2
> .
> .
> .
> etc
>
> I need to do two things:
>
> 1. Add a constant to the 7-digit number which always appears after the
> first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes
> 1427386 (by adding constant of my choice in this case 10000).  I need
> to apply this constant to column A of all rows.
>
> 2. Change the date which appears after the fifth double-quote to a new,
> constant date.  09/30/05 would become 10/14/05 for all rows.  I don't
> care what date is in that position, I just want to write over it with
> the new one.  Note that the number after the third quote can be
> variable in length (e.g., 307 vs 900000005).  I suppose part of the
> solution could be to count (parse?) over five double-quotes to get to
> this date.
>
> All other data in the column remains the same.
>
> Here's the after-image of the above example:
>
> Cell A2:
> 09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1
> Cell A3:
> 09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2
>
> TIA for any help you can provide.
>
> Ed
> 


0
ken.wright (2489)
10/24/2005 7:45:46 PM
Part 1 anyway :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------
snip 


0
ken.wright (2489)
10/24/2005 7:54:43 PM
Is there some reason you need to use Excel for this?  CSV processing is
more quickly and effectively handled by a full blown language like
python.

I work with python to manipulate CSV files on a routine basis.  Excel
is limited to 65,536 records which can be quite small at times.  The
operations you describe above can be quite easily handled by about 10
lines of python script.

0
bhwithun (29)
10/24/2005 7:58:11 PM
And assuming your helper column is in Column B, with your date to go in say 
C1 use this in another helper column

=SUBSTITUTE(B2,MID(B2,FIND("%",SUBSTITUTE(B2,CHAR(34),"%",5))+1,8),TEXT($C$1,"dd/mm/yy"))

Also note that the first formula I gave you had a constant hardwired into it 
which was at odds with me describing the constant being in A1 - apologies 
for that - it should have read

=LEFT(A2,17)&TEXT((--MID(A2,18,7)+$A$1),"0000000")&MID(A2,25,LEN(A2))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message 
news:uhareON2FHA.2316@tk2msftngp13.phx.gbl...
> Assuming your data is all in Col A, that the data you have given is 
> representative of all your data in as much as the constant that needs 
> changing is in position 18 with a length of 7 digits, then assuming you 
> have the constant you want in say A1, then in any helper column try the 
> following and copy down as far as your data goes:-
>
> =LEFT(A2,17)&TEXT((--MID(A2,18,7)+10000),"0000000")&MID(A2,25,LEN(A2))
>
> -- 
> Regards
>           Ken.......................    Microsoft MVP - Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
> ------------------------------�------------------------------�----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------�------------------------------�----------------
>
> <ed0109@volcanomail.com> wrote in message 
> news:1130180432.814371.26130@f14g2000cwb.googlegroups.com...
>>I have been given a .csv file of several thousand rows, and I need to
>> change column A of each.  The brief cutout below shows column A of the
>> first two rows.  (I need to skip the first row, which is the headers.)
>> Kinda clumsy to have such a wide column, but the application this file
>> is to be fed after I change the contents requires all the data
>> separated by double-quotes to be in the one column.
>>
>> Cell A2:
>> 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1
>> Cell A3:
>> 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2
>> .
>> .
>> .
>> etc
>>
>> I need to do two things:
>>
>> 1. Add a constant to the 7-digit number which always appears after the
>> first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes
>> 1427386 (by adding constant of my choice in this case 10000).  I need
>> to apply this constant to column A of all rows.
>>
>> 2. Change the date which appears after the fifth double-quote to a new,
>> constant date.  09/30/05 would become 10/14/05 for all rows.  I don't
>> care what date is in that position, I just want to write over it with
>> the new one.  Note that the number after the third quote can be
>> variable in length (e.g., 307 vs 900000005).  I suppose part of the
>> solution could be to count (parse?) over five double-quotes to get to
>> this date.
>>
>> All other data in the column remains the same.
>>
>> Here's the after-image of the above example:
>>
>> Cell A2:
>> 09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1
>> Cell A3:
>> 09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2
>>
>> TIA for any help you can provide.
>>
>> Ed
>>
>
> 


0
ken.wright (2489)
10/24/2005 8:25:46 PM
I'm all for using something easier than Excel, except I'm short on
language software here :)  Can Python (or any such language) be
downloaded free, and be relatively fast to learn?  Meantime, I will try
Ken's Excel solution.

Thanks,
Ed

0
ed0109 (5)
10/24/2005 9:23:51 PM
Thanks for you quick reply, Ken.  I will try it and let you know how it
goes.  Ed

0
ed0109 (5)
10/24/2005 9:26:24 PM
You're welcome - any problems simply report back with details of what 
doesn't work and I'll pick it up or someone else will.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------

<ed0109@volcanomail.com> wrote in message 
news:1130189184.716857.172530@f14g2000cwb.googlegroups.com...
> Thanks for you quick reply, Ken.  I will try it and let you know how it
> goes.  Ed
> 


0
ken.wright (2489)
10/24/2005 9:30:04 PM
Yes it can, and yes it can. It is another scripting language, so you
probably know the fundamentals.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<ed0109@volcanomail.com> wrote in message
news:1130189031.504754.152040@g49g2000cwa.googlegroups.com...
> I'm all for using something easier than Excel, except I'm short on
> language software here :)  Can Python (or any such language) be
> downloaded free, and be relatively fast to learn?  Meantime, I will try
> Ken's Excel solution.
>
> Thanks,
> Ed
>


0
bob.phillips1 (6510)
10/24/2005 10:07:06 PM
Your formulas worked to perfection, Ken.  The only nitpick was that I
had to change the picture of the date at the end of the 2nd formula to
"mm/dd/yy", very minor thing.  Clever the way you had to handle the
double-quotes with their character representation.  Many thanks, Ed

0
ed0109 (5)
10/25/2005 6:01:41 PM
Your formulas worked to perfection, Ken.  The only nitpick was that I
had to change the picture of the date at the end of the 2nd formula to
"mm/dd/yy", very minor thing.  Clever the way you had to handle the
double-quotes with their character representation.  Many thanks, Ed


"Ken Wright" <ken.wri...@NOSPAMntlworld.com> wrote:

And assuming your helper column is in Column B, with your date to go in
say
C1 use this in another helper column

=3DSUBSTITUTE(B2,MID(B2,FIND("%",SUBSTITUTE(B2,CHAR(34),"%",5))+1,8),TEXT($=
C$1,"dd/mm/yy"))

Also note that the first formula I gave you had a constant hardwired
into it
which was at odds with me describing the constant being in A1 -
apologies
for that - it should have read

=3DLEFT(A2,17)&TEXT((--MID(A2,18,7)+$A$1),"0000000")&MID(A2,25,LEN(A2))

--
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------=AD------------------------------=AD---------=
-------
It's easier to beg forgiveness than ask permission :-)
------------------------------=AD------------------------------=AD---------=
-------

0
ed0109 (5)
10/25/2005 7:02:02 PM
You're very welcome - glad it worked for you.  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------

<ed0109@volcanomail.com> wrote in message 
news:1130263300.983850.322040@g44g2000cwa.googlegroups.com...
> Your formulas worked to perfection, Ken.  The only nitpick was that I
> had to change the picture of the date at the end of the 2nd formula to
> "mm/dd/yy", very minor thing.  Clever the way you had to handle the
> double-quotes with their character representation.  Many thanks, Ed
> 


0
ken.wright (2489)
10/26/2005 10:23:11 PM
Reply:

Similar Artilces:

PST file locaion under Win7
Hi, Where does outlook 2007 keeps PST files under windows7? Apparently under Users/myname there is no folder AppData\Local... Thank you for your help. ...

CRM 3.0 and Microsoft VPC Help
Forum Members: I am trying to install Microsoft CRM 3.0 (VPC) and Microsoft VPC 5.3.582.45. I can't figure out how to get the CRM image up and running. My CRM files are loaded on an external hard drive. Please help! Thanks, Casey Please clarify: The CRM 3.0 VPC expired on 11/30/2008 Microsoft Dynamics CRM 3.0 Virtual PC Demonstration - August 2007 Release: http://www.microsoft.com/downloads/details.aspx?FamilyId=57394BC8-5ECC-422E-A066-34189F48F8C8&displaylang=en Directions: For the VPC documentation and demonstration materials: download the VPC Documentation and Demonstrati...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

OL2007 not move big files from outbox to sent
Hi, We have 2 computers with separate email accounts on Roadrunner. One machine has XP with Outlook 2002-sp3 and works without any problems. The other has Outlook 2007 on Vista and has problems sending files over a meg or so in size. It seems to actually send the file but the file remains in the outbox folder and does not move it to the sent folder. I say it "seems" to send the file because some people complain of getting muliple copies and others don't seem to get them at all. If I hit send again (not set up for auto send) it seems to send the file again (why some ...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

How to repair a .dll file in IE8
Several days ago I noticed in my Dependency Walker that the IESHIMS.dll files has a yellow circle with a question mark on it. What does this mean and How do I repair it? OS: Windows Vista Home Premium Browser Internet Explorer 8 -- TW Hi, See the History tab on that dialog. A web search for ieshims.dll files will also help you find a solution for that file. Regards. "TW" <TW@discussions.microsoft.com> wrote in message news:63E61463-D766-4ABC-B081-BFA8C04FB159@microsoft.com... > Several days ago I noticed in my Dependency Walker that the IESHIMS....

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

unsolicited entry in the folder "Temporary Internet Files"
Hello, I am working on a programme which browses web sites and runs under XP. The http download is as follows: pServer = Isession -> GetHttpConnection(strServerName, nPort); pFile = pServer->OpenRequest(CHttpConnection::HTTP_VERB_GET, strObject, NULL, 1, NULL, NULL, dwHttpRequestFlags); pFile->SendRequest(); pFile->QueryInfoStatusCode(dwStatusCode); if(dwStatusCode == 200) { pFile -> QueryInfo(HTTP_QUERY_LAST_MODIFIED, &sysT); status.lastMod = sysT; if(DBlastMod == status.lastMod) //URL content has not changed since the last visit ...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Maximum file sizes
Is there a recommended maximum file size for Excel 2000. PC spec 2Ghz P4 with 256 Mb Any advice appreciated Deus -------------- Does Not Exist Hi have a look at http://www.decisionmodels.com/memlimits.htm -- Regards Frank Kabel Frankfurt, Germany "Deus DNE" <deus.dne@ntlworld.com> schrieb im Newsbeitrag news:1561701c41d4f$358950f0$a001280a@phx.gbl... > Is there a recommended maximum file size for Excel 2000. > > PC spec 2Ghz P4 with 256 Mb > > Any advice appreciated > > Deus > -------------- > Does Not Exist ...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

File size #11
I have read the other discussions on file sizes but they do not seem to address my problem. I have an Excel file that is 12mb large with low-res jpegs in it. This file also has merged cells to make it look pretty. Does Excel look at these merged cells as graphics? Is this why they are too big? I have run a macro to make sure that it goes to the last cell. How can I get the file smaller? How big are the graphics? If you remove them from the file, what is the size of the file and what is the size of the graphic files? To be sure you do not have extra formatting, if you open the file...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

find action on log file
Hello there I want to use outside tool to find who made some update on table in my server I know that there are many tools for this. But can they do it on simple recovery model? Roy Goldhammer (royg@yahoo.com) writes: > I want to use outside tool to find who made some update on table in my > server > > I know that there are many tools for this. But can they do it on simple > recovery model? No. If you are using the simple recovery model, the contents of the log is wasted away everyonce in a while. Well, if the disk area has not been overwritten...

Including and Executing JS and CSS from an ASPX within a Master Pa
I am running into all kinds of difficulties LINKing/Inserting JavaScript and CSS on an ASPX that is inside a MasterPage. I'm not sure what the rules or even what the life-cycle is. Here are the actual issues: 1. Do I have to use code and "RegisterStartupScript()" and all that? Is there no way to do the insertion of these in the actual HTML mark-up of the ASPX page? 2. If I am including jQuery from the MasterPage and *then* want to include a ..JS file from one of the child ASPX pages, I'm running into trouble where it would seem that jQuery has not been insta...

CSV Files and VLOOKUP error
Does anyone know why VLOOKUP and Compare formulas don't work o information originating from a CSV file? I've tried copying an pasting values only (to leave behind any formatting), but it doesn' help. Through countless tests, I've narrowed it down to the CSV file bein the only possible cause -- Message posted from http://www.ExcelForum.com Hi ajpowers, Just a guess but the imported data may have leading or trailing spaces or are numbers stored as text. You could use the formula =A1=D1 to see if you get a true or false, where A1 is the lookup value and D1 ia the CVS valu...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

Columns #4
My columns in Excel have disappeared how do I get them back? Hi Me! I'm just guessing what you mean. Use: Tools > Options > General Remove check from "R1C1 reference style" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "me" <anonymous@discussions.microsoft.com> wrote in message news:12D9050B-079C-476D-B5B6-84445F60EA51@microsoft.com... > My columns in Excel have disappeared how do I get them back? Another gue...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

MOVE TO FOLDER... only appears. I need MOVE TO FOLDER
This is very odd and I've found that I've had this problem before with not finding icons. Some I've found at the office don't appear here and vice versa, or they act differently. I have Outlook 2000 in both places! Very odd. At home, I couldn't find the HIGH IMPORTANCE icon under the list of items available in the customize. Yet I have it at work. That's the one where when the HIGH IMPORTANCE is on, it shows a depressed button state. I really need that in both places. Anyway, simple (or so I thought) - I ended up just brining the toolbar from work on a floppy, a...