Link to home
Create AccountLog in
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Flag for New Zealand

asked on

Excel Zone Expert Discussion, Number 25

This thread is for general discussion about all things related to the Excel zone and its Experts. The Excel Regulars participate here as a matter of course, but newcomers are always welcome. Topics might include:

- Asking for assistance with specific questions
- Issues, announcements, and discoveries related to this zone
- Discussing Experts Exchange features and functions
- Recognition of new Experts, amazing posts, and accomplishments
- Getting to know your fellow Experts
- Planning meets and greets with other Experts when traveling

And, occasionally, a little humor and other completely unrelated topics.

Drivel posts such as "First Post" or "Subscribing" will be deleted. There is a link to the right with the text "Monitor". Click it. It does magical things.

This thread must not be used for any of the following:

- Presenting any specific member in a negative light
- Suggesting that any specific member needs to change their behavior
- Suggesting that others blacklist any Asker for any reason

These topics and any others like them should be taken up privately with the site moderators, all of whom are listed here with their email addresses:

Previous Expert Discussion: http:/Q_26785806.html

Next Expert Discussion: http:/Q_26856971.html


Zone Advisors

When this thread reaches a page size of about 75K (about 150 posts):

1) Create a new "Expert Discussion" thread using this content as a template, incrementing the number in the title, and setting the "Previous Expert Discussion" link to this thread.

2) Edit the new thread and set the points to zero.

3) Edit this question and set the "Next Expert Discussion" to link to the new thread.

4) Make a final post in this thread with a link to the new thread "Next Expert Discussion: http:Q_xxxxxxxxx.html" and accept that post as the solution.
Avatar of rspahitz
Flag of United States of America image

For those just seeing me lurking about, I figured I'd introduce myself.
I was introduced to EE back in early '01 and found it fun, made some good friends.  I spent most of my time in the VB6 area.
Been gone for a bunch of years, but always loved the fun around it so came back when someone posted a comment in one of the old threads I was monitoring.
This time I decided to come into the Excel area.
So far I'm seeing the experts here to be so much nicer than some of the competitors we used to have in the VB world.
Anyway, I'm hooked again and will probably be wandering in and out of a few forums that I like/liked.
Hey N!  Long time no see :)
Avatar of Dennis Wallentin

I'm Dennis aka XL-Dennis and it's nice to "see" You here :-)

Was it for the same reason You also decided to skip VB.NET?

Kind regards,
Hi Dennis,
I use VB.Net and C#.Net a lot, but Excel is just so much more fun.
I just drifted because I got so busy with so many other things, then forgot to come back.
Honestly, I can't wait until the inevitable integration of Office with DotNet, but there's definitely a learning curve.

Let us see what MSFT brings in the next release of Excel 15. It's my understanding that MSFT didn't managed to add a new language in the Office suite for the latest version 2010. I guess it will be a challenging for all of us.
Except for those guys that believe they  can get retirement before that version is shipped.

Yes, I can understand the joy with Excel and if the day comes when there is no longer any joy with it it's time to move on. After all, who wants to be placed in a "cell" for the rest of the life?

Kind regards,

can anyone please give their inputs on this question.. i think it was improperly graded.


any assistance would be appreciated.

Hey...what's a good way to report an issue (aka bug) to Microsoft?

I have the following text in a cell (cut short a bit, and which may or may not have pasted correctly here):


The text contains some non-printable ASCII codes (28, 29, 30) that appear only then I use the Terminal font on Excel 2010.
However, the formula box seems to use a different font that ignores the width of these characters.  That means that if I click in the formula box to edit the text, the cursor ends up in the wrong place and I delete/insert text in the wrong position.
If I edit directly in the cell (only when using the Terminal font) then everything is fine.

Oh, and one more thing: I can paste the entire contents into the cell and it retains these ascii characters, but if I try to paste just one by itself inside the text somewhere, it puts a space.  I can even copy the single character directly from the edit window but when I paste it in that same window, it's a space (but it's correct if I paste into Notepad.)
that looks like a source for existing bugs, not for reporting new ones.
However, it led me to the support area where, for $99, I can submit and issue for them to review. /sigh/
You have to pay $99 to report a bug?

The Connect site should be the logical channel. OK, if You cannot report let us know as there are some MVPs here around.

Kind regards,
I have to pay $99 for support unless I have an MSDN subscription.  Presumable they will either not charge me the $99 or reimburse me when they discover this as a bug, but who knows, and why should I have to pay anything to help them fix their product?

So, XL, yes, if you can report the above, that would help.  Here's the description I tried to send:

Excel 2010:
in-cell editing works fine but not properly in the formula bar, when using Terminal font in the cell with text containing characters ASCII code 28, 29 and/or 30
There is a provision for MVPs to report bugs without paying the $99. The expectation is that we be able to reproduce the problem and list the exact steps to recreate it before reporting it. Depending on severity (number of people affected and existence of a workaround) plus where they are in the cycle of delivering the next version, the bugs may take a year or more to be paid attention to.

I have reported several bugs in the past, but each time the issue was closed without resolution because the Microsoft tester was unable to reproduce it.

With that preamble, how does one recreate the problem? There are no ASCII 28, 29 or 30 characters in the string you posted. And the only differences I saw between formula bar and a cell formatted in 9 point Terminal are the wider spaces in Terminal font and changing the ellipsis character at the end (in the formula bar) to a lower case a with accent grave (Terminal font).

I created a test string with the formula:
="A" & CHAR(28) & "B" & CHAR(29) & "C" & CHAR(30) & "D"

I then did a Paste Special...Value to get several copies of ABCD separated by the non-printing characters ASCII 28, 29 & 30. I then changed one cell to Terminal font and another to Calibri.

In both Calibri and Terminal cells, I was able to select the non-printing characters in the formula bar (where they showed up as an open square with question mark inside) and delete them. I could do the same in the cell.

What exactly is the problem?

byundt: Thanks for that info. I didn't know about that. So supposedly if I pay $99 and then report a bug, then what happens? Would it still take approx 1 year (in worst case scenario). What happens when we get the message if something goes wrong in Excel and a pop up comes asking us to report the bug or not?

If you pay the $99 and report a bug, they refund the $99 if it really is a bug. They will also triage your problem faster with the $99 than they do via the MVP backdoor.

The fact that Microsoft can't reproduce bugs that I thought were reproducible should tell you something about the number of valid bugs that are actually reported.
You report the bug. It's done automatically when you click the button. No fee required.
The popup on reporting a bug or crash to Microsoft works on the basis of numbers. If a lot of people are reporting a problem and it has a recognizable "signature", then that issue percolates to the top.
Kevin: I was wondering about that. Then why charge $99 after all?

Ah! Ok...

So basically to filter out the actually bugs... Got it...

byundt...two things:

1) if you copy the text I have in the message above and paste into notepad, you will see the symbols if your font is terminal, but your creation may work (although I think it also requires that the text be long enough to wrap to the next row of the formula bar

2) I noticed that on Windows XP, I see the little boxes but in Windows 7 I do not.  I'm not sure why windows 7 treats fonts differently but I can show print screens of the difference.
OK, I added this formula into a cell:


When I show it in terminal font, it looks good in the cell.

Copy and paste value into another cell...only see A and Z until I change to Terminal font
however, in the Formula bar, I see AZ but if I click, I can cursor through the 5 characters.  The problem is that there is no space (or boxes showing between the A and Z so it appear to be AZSpaceSpaceSpace when it's really ABoxBoxBoxZ

That means that if I want to erase the Z, I don't move the cursor to where the Z is show (2nd position) but rather after the 4th position and press the delete key (or after the 5th position and backspace.

So basically, all the functionality is there, it's just not displaying it right in Windows 7 / Office 2010.  I'll check Office 2007 later and XP with Office 2007 if I have time.
I suspect that you have a video or printer driver issue in Windows 7. Office applications try to be What You See Is What You Get (WYSIWYG), so any bugs in either the video or printer driver affect what shows up on the screen and prints. A printer driver issue can affect your display, or a video driver issue affect your printout--as well as the like to like interaction you would expect. User generated image
Interesting thought...the problem is on my company laptop with Windows 7 with Excel 2010 and also with home home computer with Win7 and Excel 2007.  Also another laptop at a contract job with Win7 and Excel 2010.
My WinXP desktop at work didn't seem to have that problem with Excel 2010, as I recall...but I'd need to confirm that,
oh, also noticed it in at least two Win7 machines with a VB.Net TextBox, but not on WinXP.
Hi all,
A seriously family matter has happen today and I must take care of it. So I will not be around for a while and I have will not have any computer available.
The only thread that I has ongoing is in VSTO zon:

If the person still have issues then I guess I cannot contribute with more.

I will let You know when I'm back in business.

Kind regards,
Sorry to hear that, Dennis. Hope it resolves itself well.
Question about basing an Excel 2007 PT on an external text file:

I had assumed that Excel would have no difficulty in using a 4 MM row text file as a PT data source, but when I actually tried it I could not get it to work.

The same data works just fine when imported into an Access table, though...
Works OK for me using the Jet OLEDB provider in a quick test with 4m rows (only two columns though I doubt that makes a difference)
Another MVP is amongst us today: SmittyPro,

If you see Smitty around, please give him a warm welcome :)
Hello All

May I request some of you to please visit this thread and run the tests for me?

How many such MicroSites are there?

E-E has much better/professional interface than these other sites - plus all the tracking for Experts and Knowledgebase is NOT on those sites, even tho some of these questions here appear to appear there...

Thanks Modalot: Learnt something new today :)

Yeah, I was invited to the pre-launch beta of the Excel Answers was kinda fun but just a bunch of experts asking silly questions among each other like "How do I add a formula to cell A1?"  And "why do I get an error when I add the formula =A1 in cell A1?" :)

But we all complained about the cheezy characters, too.

I have never been able to log into any of the EE micro sites with my credentials.  I gave up.  :-(

Have you changed your EE password anytime in the last few months? Try your old(er) password(s).

I had changed my after my e-mail got hacked. My old password works on the microsites.
I tried both former and current passwords.
Didn't know the backbone of EE is Oracle.
"Database Administrator (WEB)  # Oracle Certified (OCP)"

For a moment there I thought they were paying 401 K for an Oracle DBA.  Boy was I disappointed.
We offer a great benefits package including medical and dental benefits, 401K, paid vacation...
It was me. But now that I realize they aren't paying 401k I'm resigning. Go ahead and apply.
My sympathy to teylyn, cyberkiwi and everyone else from New Zealand. I hope that you and your families are far enough from the epicenter to avoid harm. The pictures of the devastation in Christchurch leave one fearing the worst.

Oh...are they from best wishes too.  If we can help out remotely, let us know.
I am simply out of words...

I hope every one is safe in your family.

Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)


Hi all, this shake was a real bad one. A lot of IT companies in CHCH, so I wouldn't be surprised if experts or members are there.

Feel for them all.
>>this shake was a real bad one.

Yeah. The news reported it to be on a scale of 6.5?

The pictures looks awful... did you guys feel the quake on your side? how are things in Taranaki?
There's no way we can express our sympathies to you all, but fingers crossed for those trapped
Yeah, I saw similar pictures in other links as well. Reminds me of the earthquake that we had in India :(

I sincerely hope experts and their families from Christchurch and safe.

I did not feel it up here, and I don't think anyone did. Taranaki is fine. It's weird to sit in a climatised office trying to work while people are dying in the rubble a few hundred km South.
Last April, we had a 7.1 100 miles (~150km) east of San Diego.  We all felt it and it was scary, but minimal damage, and the epicenter was in an area that's primarily farmland.  We got lucky on that one, but the aftershocks still continue as much as 5.1 last week.
Sid, isn't showtrails a Windows function, where the mouse leaves a ghost trail behind as it moves and it slowly fades away?  sounds like this guy a javascript version and wants something like that for excel...tell him to just set it at the windows level
Control panel, mouse, pointer options, [x] display pointer trails
hmmm...after reading further, that's not it...this author is very confused...
rspahitz: Initially even I thought that but then it doesn't happen in that link. What I could understand is to show a preview kind of image when you hover a mouse over an image.

See what the OP says in ID: 34978756

Also I might me wrong but I didn't see any piece of Java code that does what you are hinting at.

xx over

>> hmmm...after reading further, that's not it...this author is very confused...

Yeah I know... so I thought maybe different pair of eyes need to look over that thread.

it would be interesting to follow up in the HTML topic area to see if author starts asking how to do this in excel :)
Although it can be done in excel (not easily through html) there are much better tools to handle that.  Maybe time to move up to a real data application rather than a formula-based application.

I want to respond with the below - what do you guys think?  Should I respond accordingly, ignore the question, or just give the solution and move on?  Seems to be some grumbling out there and not sure why...:

Ahh, an opportunity for E-E exchange and learning, so here goes...

I've never read a book, manual, or web page on E-E etiquette in the context you describe.  My experience is E-E experts contribute on everything they believe they can contribute to, some one-upping the others in a fair comradare to ensure the best solution is provided, and learning experience, exchange occurs.  There is nothing gained by responding to a question for the expert, other than the opportunity to contribute, to learn, and to get points which mean almost nothing beyond the ego boost gleaned from helping someone else.

You can view each E-E expert's qualifications before reading the response if you choose, but then all E-E experts started out with 0 points and 0 certifications - I was there 2 months ago, because I never contributed before then, though I've been a member since 2004.

The solution to this question is fairly trivial and I believe most E-E experts can handle.  In fact, with a bit of thought and guidance, you can do this yourself!

Just write a simple vba routine looping through the used range of column A
  within that loop
     check the font style and leading spaces of each cell that has data
        based on font style and leading spaces, change the value of the cell by trimming it, then padding with # spaces based on your "rules"

That's it!

If you're unsure how to write this code, just ask!

Hello Dave,

I can see erp1022's dilemma a little, I think. He wants a good solution and wants to ensure that he gets one without offending - he's quite polite about it. Perhaps he's had a bad experience before, here or somewhere else.

Having said that I think he would be very unlucky if that happened because I don't recognise the behaviour he describes - at Experts Exchange or any other forum I know.

I think most experts here are respectful enough of their peers to leave another expert to it, if the solution provided is the right one - but I'd certainly step in to any question if I thought the answer given was wrong or insufficient....and I thought I could improve on it - I have done that and I continue to do so. In my experience many others here do the same.

I considered replying myself - I don't personally see anything wrong with your proposed answer - go for it - the Asker already has 2 possible solutions so it's not like you are complaining while withholding the knowledge......

regards, barry
I'm probably a bit guilty of that...if I see a question that is already being properly answered, I won't bother to put in my variation unless it adds a different perspective.  However, sometimes the asker doesn't ask the right question and the discussion goes in a different direction where, if I had joined in, I might help solve the problem better.

So I think the author was cautious about getting an inadequate answer and not having any other experts joining in to offer other perspectives.

To me the dilemma is that I don't want to step on anyone's toes by submitting a very similar answer (and then maybe "stealing" points from the first person who offered a good answer); but I also like to share in some of the discussions because they often expand my knowledge of how to solve things in general.  As such, I tend to be rather picky about which topics I join and usually only look for the ones with zero or one comment. there a way to get how many non-Author comments are in a thread?  I think that would be a better marker than total number of comments...
Agreed.  I try to only contribute if I have another option, or fix perhaps syntax, but especially if comments are low or its an older/neglected question...

What tools does everyone use to get a picture that is pasted in an E-E answer?  I do an ALT-PRINTSCREEN then paste into powerpoint as embedded metafile, then I click on that picture and save.

There's gotta be a better way...  Suggestions?

Too bad the E-E interface doesn't support clipboard pasting, or does it?

I save it in paint brush instead of PP.

paintbrush for me too so it's stored as a native image
I get the save as bit, lol.  Is there any better tool to capture what you want on the screen and save it in one step, instead of opening powerpoint, paint, excel, or whatever, then pasting as some type of image then saving.


A decent, easy, open source photo editor is  Photoscape.

User generated image
I would discourage saving as bitmap -- sucks up space and load times.
The jpg is about a universal standard.
The png (Portable Network Graphics) is  about the best for size to quality.

The mosaic function of Photoscape allows me to put an image up -- but protect the areas askers/experts don't need to see.
I think the question is:  how do we post an image from memory into and EE message (not an image file, but the actual image contents, bit-stream, binary...whatever you want to call it)?  I don't think that most sites (if any) allow that, but it seems that they could by simply making it a sort of FTP-from-clipboard function.
I think the question is:  how do we post an image from memory into and EE message

I don't think there is a direct way. It either has to be on the web, or an upload. There is really no middle ground.
I use SnagIt (licensed version my workplace installed on my work lapper)  to create a screenshot to upload.

If you run Office 2010, you don't need any external tool, though. Use Word or Powerpoint to take screenshots of full windows or screen clippings of partial areas of the screen. Easy as. Use the drawing tools to highlight bits in the screenshot, crop to size, compress if you want. Then right click the image in Word or Powerpoint, save to disk and upload. Funnily enough, the "Save as Picture" command is not available in Excel.

I still use SnagIt for screen delays, for instance if I want to take a screenshot from a dynamic element, like a dropdown or dragging activity. See screenshot.

 User generated image
Whatever you use, please remember to save the screenshot as something other than BMP, which will be huge. GIF will get the smallest file size. JPGs will be a touch blurry. PNG is a good option, too.

cheers, teylyn
Thanks EEple.  SnagIt comes at a cost, but would be nice to not to have so many steps...  Anyway, I was running into IE crashing when I'd attach a file or code or image, sometimes.  Not sure why.  And, I'd have to repost.  I've found if I slow down and pause after hitting FILE (when it loads the directory listing), then cautiously move my mouse up to select what I wanted to upload, and it hasn't crashed since.  Seems no one has reported this error before and hopefully it doesn't happen again...

I was typing away, having to copy/paste my response in notepad, etc., on the offchance I'd have a crash... lol


EEple -- Experts Exchange people. Also refers to the icons that show's your ranks in the Premium skin.
Sid - congrats on recent GENIUS status!

I have to say its because of you that I decided to Expert up and contribute :)

well done, my man!

just curious...if you can upload a file from the hard drive, why can't you "upload" a file from the clipboard by simply adding appropriate header information...I guess this would have to be done by the OS, not the website, but that sounds like a great browser add-in...
The website could do it if it wanted. Websites can interact directly with the clipboard if the right languages are used. (You'd need more than HTML)
Thanks David :)

But there is one serious major side effect that happens when you try and answer more questions... So you wanna be careful?

Sid - agreed.
woo hoo! :)
"You have achieved Master Certification in Microsoft Excel Spreadsheet Software by earning 50,312 points!"
Congrats Sid...I'll catch you eventually :)
rspahitz: Great Job rspahitz:!!!! Keep it up!

>>I'll catch you eventually :)

Amen to that :)

hmmm.that didn't come out right...that quote was my new ranking, but I also wanted to congratulate Sid on his new GENIUS status
"You have achieved Master Certification in Microsoft Excel Spreadsheet Software by earning 50,312 points!"

Congratulations - the first of many T-shirts, I have no doubt! :)

Apologies for the delay, but congratulations on hitting Genius - that was pretty quick! :)

No worries ;)

Thanks Rorya :)

But there is still 25,324 more points to become a Genius in Excel...

"Congratulations - the first of many T-shirts, I have no doubt! :)"

Actually, this is my second.  I was very active in the VB area 10 years ago and there was much talk of t-shirts but nothing ever came of it.  then I move on to other things and 1 day about 2 years ago, a t-shirt mysteriously showed up in the mail declaring me a was pretty awesome to see that :)
So this makes 2...maybe time to move on to a new topic :P

In that case: hurry up - what's taking you so long? 8^P

The predictions say 10th of March!


Wow, you actually have points you didn't get from Excel? Good for you! Way to branch out.
It's unavoidable with the zone system here. I have points in zones I've never heard of (e.g. Eclipse and Ghostscript PDF software!)
I don't think I'll hit the ranks of Excel Genius for a while. I kind of dropped back in Excel and mostly only hit ones that cross post with Math and Science or Algorithms. I like the little zones because I can spend all day helping my wife with the new baby and still stay on top of the zone.
Wow... i never knew excel was so powerful. :)

According to this wesbsite,Podnova there is template to track your er, umm... how to put it delicately, your daily movements too.

See image for more clarification.

tommy: " I like the little zones because I can spend all day helping my wife with the new baby and still stay on top of the zone."

Good strategy for being top in the rankings too :)
I'm looking forward to the next one as I figure I won't be sleeping anyway, so I get to post more.
Thanks for the link to Patrick's updated Genius watch.

Based on YTD puts it a year after Based on History! Most of the Jan points are really carry overs from last year, so I doubt I'd make Genius by then.

I only come around to interesting SQL Server question nowadays, it's an exception that twice today I have stepped on Kevin's toes.

[pre]Congrats Sid on being Excel Genius - see you in the (site) 4-mil club at EOY given your amazing rate.

Rory I doubt you'll recover from this  but I predict we will have a new Excel king by the end of this month.
What is the "this" from which I will not recover?
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account