MS-Excel Expert
For Excel Training and Automated Tools, Please contact Vishesh Golya on visheshgolya@gmail.com or +91-98289-88829
Monday, January 8, 2018
Thursday, January 4, 2018
10 Best Telephone Vocabulary and Phrases
Telephone Vocabulary and Phrases:
1. May I speak to...? (क्या मैं...से बात कर सकता हूँ?)
2. I am calling on behalf of... (मैं...की ओर से फोन कर रहा हूँ)
3. How may I help you? (मैं आपकी कैसे मदद कर सकता हूँ?)
4. Where are you calling from? (आप कहाँ से बात कर रहे हैं?)
5. I'd like to speak to... (मैं...से बात करना चाहूँगा)
6. I'm afraid you've got the wrong number. (मुझे डर हैं आपने गलत नंबर मिलाया है)
7. Your voice is cracking. (आपकी आवाज कट रही है)
8. I can't hear you too well. (मुझे आपकी आवाज ढंग से नहीं आ रही)
9. Could you please repeat what you just said? (आपने अभी जो कहा क्या आप वह दोहरा सकते हैं?)
10. I'm afraid I can't hear you. (मैं आपको सुन नहीं पा रहा)
11. Would you like to leave a message? (क्या आप कोई मेसेज देना चाहेंगे?)
Holding the line:
1. Just a moment, please. (एक मिनट)
2. Please hold on (कृपया होल्ड करें)
3. Hang on (होल्ड करें)
Wednesday, January 3, 2018
Overwrite an Existing File Using VBA Code in Excel
This tutorial will help you in, allowing your Macro/VBA code to overwrite an existing Excel file.
This will allows you to do things like:
Export a weekly report to a specific file and overwrite that file each week.
Note: The technique in this tutorial does not require any confirmation in order to overwrite the file.
Add these two lines to any macro to allow them to overwrite a file without having the confirmation window appear:
This will allows you to do things like:
Export a weekly report to a specific file and overwrite that file each week.
Note: The technique in this tutorial does not require any confirmation in order to overwrite the file.
Overwrite A File Using VBA
Add these two lines to any macro to allow them to overwrite a file without having the confirmation window appear:
Tuesday, January 2, 2018
Excel Function to Extract a Word or Text from a Cell
Excel UDF function that makes it easy to extract a word or text from cell in Excel.
This is a single function! It does NOT require a complex formula or nested functions or anything like that. To use this function, we first need to create it using a UDF, User Defined Function. You can learn about this kind of function here: User Defined Functions in Excel
(UDF code for this function listed in the next section)
(If you do not know about UDFs, please visit the link at the top of this tutorial. Here, I assume you know what they are.)
Here is the UDF code:
Copy & Paste the above code into a module in the VBA Editor (Alt + F11 and then Insert > Module) and then go back to Excel and we can begin to use it.
Here is our sample:
Let's get is from this sentence using our new function.
Notice that the second argument is a blank space surrounded by double quotation marks. This is because space is what separates the individual parts of the text in cell A1.
The third argument is 2, which says that we want to get the second word from that cell, which will be is.
This follows the same pattern as above.
I want to get the number 234 from this cell.
Here, the delimiter is a dash, and of course, it needs to be surrounded with double quotation marks. The 2 in the third argument means to get the second word or element from the cell. The function breaks the original cell into parts based on the delimiter that you enter and you just need to tell Excel which part you want to get back.
If you didn't understand everything in this tutorial, please read the UDF tutorial that is linked to at the top of this tutorial. That will get you up-to-speed on how these types of custom functions work in Excel.
This is a single function! It does NOT require a complex formula or nested functions or anything like that. To use this function, we first need to create it using a UDF, User Defined Function. You can learn about this kind of function here: User Defined Functions in Excel
Syntax
(UDF code for this function listed in the next section)
=Get_Word(input_data, delimiter, word)
Input_data | The cell or text from which you want to get a word or text. |
Delimiter | The character that separates your text. In a sentence, this would be a space. It could be a dash, for things like part numbers, or, really, anything that separates your text into individual sections or elements. If the value in here is not a number, it must be surrounded with double quotation marks "". |
Word | This is a number. The number is which word or individual section you want to get from the text. 1 means get the first word/section in the cell; 2 means get the second word/section in the cell, etc. |
Create the Function and Install It
(If you do not know about UDFs, please visit the link at the top of this tutorial. Here, I assume you know what they are.)
Here is the UDF code:
Function Get_Word(input_data, delimiter, word)
'make input number more friendly for users
word = word - 1
input_data = Split(input_data, delimiter)
Get_Word = input_data(word)
End Function
I only included one comment above because the rest of the code is self-explanatory if you know VBA, and, if you don't know VBA, it doesn't really matter, because it works already.Copy & Paste the above code into a module in the VBA Editor (Alt + F11 and then Insert > Module) and then go back to Excel and we can begin to use it.
Function Examples
Get a Word
Here is our sample:
Let's get is from this sentence using our new function.
Notice that the second argument is a blank space surrounded by double quotation marks. This is because space is what separates the individual parts of the text in cell A1.
The third argument is 2, which says that we want to get the second word from that cell, which will be is.
Result:
Get a Part Number
This follows the same pattern as above.
Sample data:
I want to get the number 234 from this cell.
Here, the delimiter is a dash, and of course, it needs to be surrounded with double quotation marks. The 2 in the third argument means to get the second word or element from the cell. The function breaks the original cell into parts based on the delimiter that you enter and you just need to tell Excel which part you want to get back.
Result:
Notes
This is a great UDF. I love how simple it is to use and how easily it does something you expect Excel to do.If you didn't understand everything in this tutorial, please read the UDF tutorial that is linked to at the top of this tutorial. That will get you up-to-speed on how these types of custom functions work in Excel.
Subscribe to:
Posts (Atom)