Split And Join Substitute And Perform Surgery On Text Strings

1 minute read Example: =LOWER(“hello”) Result: hello Convert text to upper case Example: =UPPER(D3) Result: JAMES Convert text to proper case (each word’s first letter capitalized) Example: =PROPER(“this is a long sentence”) Result: This Is A Long Sentence Combine different text values to one text Example: =CONCATENATE(A3, ” and “, A4) Result: 30 and 25 … Read more

Clear Multiple Cells Or Ranges On A Worksheet

less than 1 minute read This joins many addresses together as a string, picks up a defined named range “sNamedRange” and clears the contents from the cells. ‘================================================================================================== ‘ ## Clear data from multiple ranges ‘================================================================================================== Sub ClearReportData() ‘ // vars Dim sNamedRange As String sNamedRange = Range(“sNamedRange”).Address Dim strCells As String strCells = “E6:E8, … Read more

Add Leading Or Trailing Text To Constant Cells In A Range

2 minute read You will be prompted with an input-box for the range of cells and for the leading text. ‘================================================================================================== ‘ ## Add leading text to all the cells in a selected range ‘ You will be prompted with a input box for the range of cells ‘ and for the leading text. ‘================================================================================================== … Read more

Generate A List Based On Criteria

2 minute read Generate a list of items based on a criteria that doesn’t show any blanks or spaces. 20 Fruits and Vegetables listed below, choose either a fruit or vegetable and a list will populate without blanks or spaces. Formula for E2: remember to use Ctrl + Shift + Enter as this is an … Read more

Workbook And Sheet Name With Aormula

less than 1 minute read These formulas require that the workbook is saved Get the file name of this workbook: Formula: =CELL(“filename”,A1) Result: C:[MyWorkbook.xlsx]Sheet1 Get the file name of this workbook without the file extension: Formula: =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“.”,CELL(“filename”,A1))-1-SEARCH(“[“,CELL(“filename”,A1))) Result: MyWorkbook Get the active sheet name: Formula: =RIGHT(CELL(“Filename”,A1),LEN(CELL(“Filename”,A1))-FIND(“]”,CELL(“Filename”,A1))) Result: Sheet1 Reference another sheet name: Formula: =”‘”&MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)&”‘!” Result: … Read more

Get The Workbook And Sheet Name With A Formula

less than 1 minute read These formulas require that the workbook is saved Get the file name of this workbook: Result: C:[MyWorkbook.xlsx]Sheet1 Get the file name of this workbook without the file extension: =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“.”,CELL(“filename”,A1))-1-SEARCH(“[“,CELL(“filename”,A1))) Result: MyWorkbook Get the active sheet name: =RIGHT(CELL(“Filename”,A1),LEN(CELL(“Filename”,A1))-FIND(“]”,CELL(“Filename”,A1))) Result: Sheet1 Reference another sheet name: =”‘”&MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)&”‘!” Result: ‘Sheet1! Tags: lookup-reference, text-strings Categories: … Read more

Function To Test If Any Sheets Are Protected

less than 1 minute read Tests if any sheets in the active workbook are protected with or without a password. ‘============================================================================================ ‘ ## Tests if any sheets in the active workbook are protected with or without a password ‘ Insert before your code to validate if any sheets are protected ‘ ‘ Test if any … Read more