Related Posts

19 comments

Kanwaljit

Hi Bastien,

May the couple live 1000 years !

My wishlist is getting longer.
The in-built "Text to Columns" utility of Excel is somewhat limited in its approach. Perhaps there should be More than one "Other" delimiter boxes so that if one wants to separate for / , \ , ) , ( at the same time he can do it .

Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

Thank you for thinking along.

I can see the benefit of such a tool. However in which kind of (business) situation would you need such a tool. In other words, what kind of documents do you get that should be splitted like that. Perhaps it is possible to convince people that they should provide their data in a better (separated) format.

Kind regards,
Bastien

Kanwaljit

Hi Bastien,

May the couple live 1000 years !

It spurred from a recent requirement to separate a file address into separate components. And the file address contained atleast two delimiters / and . (because even the document type was to be separated). Presently I can provide with the above example only. Not convincing enough though other peoples input may provide other scenarios too…..

Regards
CA Kanwaljit Singh Dhunna

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

One more scenario regarding above. We are downloading our bank data in excel and most of the times it had to be splitted using more than 1 delimiters.

Regards
CA Kanwaljit Singh Dhunna

Wanderer

Hi everybody,

it would be nice to have an utility, that will split a cell not only into columns, but into rows too. Afaik, there's no easy way to do it in excel without help of 3rd party utils.

And many thanks for ASAP, of course :)

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

Please guide me !
I am maintaining a file in excel for banking entries and it contains VBA code for its working. With the passage of time the code keeps changing. I want to maintain the "Bank Book" version wise. But don't know when to give a new version number to it .

Regards
CA Kanwaljit Singh Dhunna

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

First of all, kindly increase the width of this comments box. 2ndly Today I used the "Delete all objects on the selected worksheets" on one of my worksheets. This sheet contains validation on 4 columns having 1000 rows each (In-cell dropdown check box is "checked", off-course). But the validation dropdown menu on all the columns have ceased to appear. Whereas there is no problem on all the other worksheets. Also the Filter Dropdown on the Header Row are intact. I suspect it happened due to the use of above Utility. But you can verify it better :)
If yes, it would be better to give options for which objects to delete and which not.

Kind Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

Thank you for your feedback.

As you will see now the comment box is wider now.

The utility does indeed remove the dropdown arrows from the cell validation dropdowns. Apparently the code "shapes.delete" will also remove these dropdown arrows. I will update this / rewrite the utility in version 4.3.0 which will soon be pre-released.

Kind regards,
Bastien

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

Thanks for the BIG comment box. It seems finer now. I will wait for the next version. For the last 3 months I am battling with an issue, but couldn't get around it. After trying most of my sources, very hesitatingly I am putting it to you for guidance. In a Workbook I have database for 4 banks. For Printing Cheques from Excel I have a separate sheet for Each Bank having customized dimensions for that particular bank. But I want to change it, so that all that can be accommodated on a single Userform having 6 text boxes. Now I have maintained a dimensions (in cms) database containing Bank Name, Name of the Text box, Width of the text boxes, Height of the text boxes, distance between different text boxes and the distance of each text box from the top edge and left edge. What I wish is that whenever I select the name of the Bank from a Validation List, the dimensions of the Userform changes as per the Dimensions Database for that particular bank. Being a greenhorn in VB, till now I only have Userform alongwith the text box on it. Don't know how to do the dimensions trick and how to apply the dimensions vlookup for different banks. Can you guide me in the proper direction.

Kind Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

I would just make the userform so that the information from the largest textboxes can be shown.
This way the layout will stay the same, which is easier/clearer for the user.
You could create a dynamic layout by using the events from the controls on the userform, but I wouldn't recommend it in this case.
If you want to discuss it more, please send me an email.

Kind regards,
Bastien

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

In Import -> Insert Excel Files, is it possible to import multiple Excel Files at one go ?

Kind Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

No, at this moment you can only import one file at a time with that utility.
You can use the shortcut F4 or Control+J to quickly start the utility again.

I have added this suggestion to the wishlist.

An alternative is the following utility which can import multiple files into your workbook:
Import » Merge files together in a new file…
//www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=194&utilities=Import

Kind regards,
Bastien

Kanwaljit

Hi Bastien,

May the couple live 1000 years !

I tried the Text->Start Each word with uppercase on the following sentence

kanwaljit singh dhunna zira india
kanwaljit-singh-dhunna-zira-india
kanwaljit/singh/dhunna/zira/india
kanwaljit-singh/dhunna.zira india

and got the following results

Kanwaljit Singh Dhunna Zira India
Kanwaljit-singh-dhunna-zira-india
Kanwaljit/singh/dhunna/zira/india
Kanwaljit-singh/dhunna.zira India

whereas I wanted the results to be same like the first result. All words starting with a Capital Letter & separated by a single space.

It seems that space is the only delimiter used as the criteria for recognizing the beginning of a "word". I feel user should have some more flexibility regarding the selection of the delimiter / delimiters to be used as a starting point of a word. And it would be great if there are choices like
"either" / "or" / "and" between the selection of two or more delimiters opted by the user.

Kind Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

Thank you for your feedback.

It currently only converts words to uppercase, which means the first word and words that are separated by a space.

A request is already on the wishlist to make this more flexible so that for example you can set to have letters after a certain character converted to uppercase too.
As a workaround you can use the =PROPER() worksheet function. This function capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. In your situation this would work, but that function will also convert a string such as "2-cent's worth" to "2-Cent'S Worth".

Kind regards,
Bastien

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

Then I would also wish for a AsapProper(Text,delimiters) function to where delimiters are the delimiters to be used for recognition of a word and which are to be specified by the user.

Kind Regards
CA Kanwaljit Singh Dhunna

Kanwaljit

Hi Bastien,

May the couple live 1000 years !

I used the "Objects and Comments > Show Flowchart Toolbars" and closed excel after some time. But when I reopened Excel, the Flowchart Toolbar reappeared without any action on my part. Is it Ok ?

Kind Regards
CA Kanwaljit Singh Dhunna

Kanwaljit

Hi Bastien,

May the couple live 1000 years !

In Excel 2007, In "Customize the Quick Access Toolbar", when I selected the "Asap-Utilities"Tab from "Choose Commands from" menu, an error message appeared, and after clicking "Ok" on that error message for several times, the "Asap-Utilities"Tab appeared and that too without the Tag "No Label"

Kind Regards
CA Kanwaljit Singh Dhunna

Kanwaljit`

Hi Bastien,

May the couple live 1000 years !

In Excel 2007, In "Customize the Quick Access Toolbar”, when I selected the "Asap-Utilities”Tab from "Choose Commands from” menu, an error message appeared, and after clicking "Ok” on that error message for several times, the "Asap-Utilities”Tab appeared and that too with the Tag "No Label”

Kind Regards
CA Kanwaljit Singh Dhunna

Bastien

Hello Kanwaljit,

About the flowcharts, that is normal, they stay on the screen, but you can easily close the toolbars.

The empty popups in Excel 2007 are because of a bug in Excel.
This happens when you use dynamic labels/images in the ribbon, which is what ASAP Utilities uses.
This causes empty Visual Basic popups when you want to customize the ribbon (quick access toolbar, qat) and choose "all commands". If you first select the ASAP Utilities tab and then customize the ribbon it is okay, if you directly customize the ribbon you will still see one empty Visual Basic error. However this error has no effect on how things work. Unfortunately there is no way to trap or customize this behavior.

So if you click on the ASAP Utilities tab first and then customize the QAT then you do not get the popup.
I have no other workaround for this and hope MS solves this bug in the next service pack.

Kind regards,
Bastien