Microsoft Office Word, Excel and Power Point Basics, Tutorials, Tips and tricks
Excel VBA macro to format abbreviated data to numbers. For example, 1.1k to 1100 (k to thousands), 2.1lacs to 210000 and 1mil to 1000000. The advantage of this macro is that you don’t need to format each number. The macro detects the text (k, lacs, mil and cr) and does the math. Before you run […]
Excel function to combine multiple cells into one separated by comma. This is the quick way to combine cells separated by any specified delimiter. Use TEXTJOIN() function, which combines the text from multiple cells or ranges separated by the specified delimiter (comma, semi colon or any other). Syntax of TEXTJOIN function TEXTJOIN(delimiter, ignore_empty, text1, [text2], […]
Add a row as first row and add numbers in columns as 1,2,1,2 etc. Then highlight your entire range of data. Go to Data menu -> select Sort and Options… Change your Orientation to “Sort Left and Right”. Choose row 1 as sort by and Smallest to Largest. Excel will be changed with alternate column […]
If you want to combine multiple columns in excel into one column in excel using excel VBA macro, then use below code. This macro combines data in multiple columns into one column. When you run the macro, it asks you to enter the number of columns to combine (starting from column A), For example, If […]
We can create new line/row inside a cell by pressing ALT+ENTER in MS Excel. If you come across a situation where you want to remove blank rows/lines inside the cell, then you can’t remove them using trim method. Below code helps you to remove them automatically using VBA code. Sub removeBlankRowsInsidecell() Dim cellWithBlankRows, cellWithoutBlankRows ‘Remove […]
Excel VBA code to convert a string to uppercase letters. It converts all lowercase letters to uppercase. Sub convertStringToUpperCase() ‘macro to convert a string to uppercase letters exString = “helLo how Are yOU” ‘ Example string stringAfterConversion = StrConv(exString, vbUpperCase) ‘ Converting the string letters to uppercase MsgBox (“string after conversion to uppercase is – […]
VBA macro to find duplicates in excel sheet. This macro finds the duplicates in user selected range and highlights them in light red color. Also see VBA macro to remove duplicates VBA code to find and highlight duplicates Sub findAndHighlightDuplicates() Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With […]
Simple VBA macro to create drop down list from selected cells in your excel sheet. Select the cells for which you want to create drop down list Run the macro. Drop down list gets created in column next to the selected cells (with down arrow) Click on down-arrow button to see the list Notes: You […]
VBA macro to turn text URLs to clickable links in excel spreadsheet. This macro Checks whether cell has text URL or not If the cell contains text URL, then it turns it into clickable URL Moves to cell in the next row Repeats the steps from 1 to 3 until it reaches an empty cell […]
Excel VBA macro to extract file name from file path. This macro extracts the string (file name) which is between dot (.) and backslash(\) delimiters (from right side). Sub extractFileNameFromFilePath() ‘ This macro shows you how to extract filename from filepath ‘ It extracts the filename which is between delimiters dot and backslash filePath = […]