How to Save a Range Selection to Text File in Excel

This post will guide you how to Copy a Range of Selection to a Text file in Excel. How do I copy/paste a range from the current worksheet to a notepad text file with VBA Macro in Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:B5 in your worksheet, and you wish to copy this range to another worksheet or a notepad text file, and save it as a .txt or xlsx. You can copy the selection to the clipboard by manually, open up a Notebook file or what ever text application you use, or you can open an blank worksheet, then paste it in there and save as the file. This method can work well, but it is not a good way. You can use an Excel VBA Macro to accomplish it quickly. And this post will show you how to use an Excel VBA code to save a range of selection to a text file.

1. Save a Range Selection as Text File

You can use an Excel VBA macro to save the selection as a text file without copying and pasting to a new file. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub SaveSelectionAsTextFile()
    Dim myFolder As String
    'Save Range as Text File.
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range to be copied", "SaveSelectionAsTextFile", myRange.Address, Type:=8)
    ActiveSheet.Activate
    myRange.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

save range selection to text file2

Step6: Select one range to be copied. Click OK button.

save range selection to text file3

Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.

save range selection to text file4

Step8: open the newly created text file test1112.txt in your local disk.

save range selection to text file5

2. Video: Save a Range Selection to Text File in Excel

This video will demonstrate how to use VBA code to save a range selection to a text file in Excel.

How to Change Multiple Pivot Table Fields in Excel

This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once with VBA in Excel.

Assuming that you have created a pivot table with your data. And as the default setting for numbers is to sum the data for all numeric fields and I want to change the count of function to sum of function. You can change each of columns using the value field setting option or you can also use an VBA macro to change all fields at once.

1. Change Multiple Pivot Table Fields by Manually

If you want to change multiple pivot table fields, you can change the function in the Value Fields Settings, just do the following steps:

Step1: select one filed in your pivot table, and right click on it, and then choose Value Fields Settings from the dropdown menu list. And the Value Fields Settings dialog will open.

change multiple pivot table fields1

Step2: select Count function in the Summarize value field by list box, and click Ok button.

change multiple pivot table fields2

Step3: you would see that the sum function has been changed to count function for your selected field.

change multiple pivot table fields3

Step4: you need to repeat the above steps to change other’s fields for your pivot table.

2. Change Multiple Pivot Table Fields by VBA

You can also convert all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel. Just do the following steps to run the macro:

Step1: select your pivot table that you want to convert fields

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step5: paste the below VBA code  into the code window. Then clicking “Save” button.

change multiple pivot table fields4
Public Sub PivotFieldsToCount()
    Dim pf As PivotField
    With Selection.PivotTable
        .ManualUpdate = True
        For Each pf In .DataFields
            With pf
                .Function = xlCount
                .NumberFormat = "#,##0"
            End With
        Next pf
         .ManualUpdate = False
     End With
 End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

change multiple pivot table fields5

Step7: Let’s see the result:

change multiple pivot table fields6

How to Extract Bold Text from A List in Excel

In our daily work we may mark some texts in bold to make them to be noticed clearly. And if we want to extract them from original list, we need to know the ways to find them and then move them to another range. This free tutorial will introduce you two ways to extract bold texts, the first one is to extract bold texts via Find and Replace function, the second one is to edit VBA Macro.

Precondition:

See screenshot below. Some texts are marked in bold.

How to Extract Bold Text from A List in Excel1

1. Extract Bold Text via Find and Replace Function

Step1: Press Ctrl+F to trigger Find and Replace dialog.

How to Extract Bold Text from A List in Excel2

Step2: Click ‘Options>>’ button to get more settings, then click Format.

How to Extract Bold Text from A List in Excel3

Step3: On Find Format window, click Font tab, under Font style select Bold, then click OK.

How to Extract Bold Text from A List in Excel4

Step4: After clicking OK, go back to Find and Replace dialog, click Find All to load all matched values. All bold texts are displayed, you can drag the scrollbar to check all of them.

How to Extract Bold Text from A List in Excel5

Step5: Press Ctrl+A on one of the loaded results, then all values are selected.

How to Extract Bold Text from A List in Excel6

At the same time, bold text cells are also selected on original table.

How to Extract Bold Text from A List in Excel7

Step6: Close Find and Replace dialog. Then you can copy them and paste them to another range.

How to Extract Bold Text from A List in Excel8

2. Extract Bold Text via VBA Macro

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

How to Extract Bold Text from A List in Excel9

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

How to Extract Bold Text from A List in Excel10

Step2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1. In this step we define a new function ‘ExtractBold’.

Function ExtractBold(SelectRng As Range)
    If SelectRng.Font.Bold Then
        ExtractBold = SelectRng.Value
    Else
        ExtractBold = ""
    End If
End Function

Step3: Save the macro. And then quit Microsoft Visual Basic for Applications.

Step4: In C2 apply the new function ‘ExtractBold’ we just created . Enter formula =ExtractBold(A2).

How to Extract Bold Text from A List in Excel11

Step5: Press Enter and verify that nothing is returned, that’s because ‘NPS001’ is not in bold.

How to Extract Bold Text from A List in Excel12

Step6: Drag the fill handle down to the end. Verify that bold texts in original table are extracted. But they are not displayed in bold after extracting.

How to Extract Bold Text from A List in Excel13

Step7: Apply ExtractBold function in D column to extract bold texts from column B. Now all bold texts are extracted. You can select all of them and mark them in bold after extracting them per your demand.

How to Extract Bold Text from A List in Excel14

3. Video: Extract Bold Text from A List in Excel

This video will show you how to extract bold text from a list in Excel using both the Find and Replace feature and VBA code.

How to Concatenate Text based on unique values in Another Column in Excel

This post will guide you how to concatenate text values based on unique values in another column in Excel. How do I concatenate cells based on specific criteria in Excel.

1. Concatenate Text Based on unique Values in Another Column

Assuming that you have a list of data in range A1:B6, in which contain product IDs and product Names. And you want to concatenate product names based on unique ID values (There are duplicated ID values in Column A), How to do it. You need to extract unique product IDs in another range, and then concatenating text values based on newly created range with a User Defined Function. Here are the steps:

Step1: you can use an Excel Array formula based on the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to extract the unique product ID values.

=IFERROR(INDEX($A$2:$A$6, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$6), 0)),"")

Type this formula into cell C2, and press Ctrl + Shift + Enter keys on your keyboard to change it as array formula.  And then drag the AutoFill Handle down to other cells until getting blank cells.

concatenate text based on special criteria1

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step5: paste the below VBA code (code from here) into the code window. Then clicking “Save” button.

concatenate text based on special criteria2
Function Combinerows(CriteriaRng As Range, Criteria As Variant, _
ConcatenateRng As Range, Optional Delimeter As String = " , ") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRng.Count <> ConcatenateRng.Count Then
        Combinerows = CVErr(xlErrRef)
        Exit Function
    End If

    For i = 1 To CriteriaRng.Count
       If CriteriaRng.Cells(i).Value = Criteria Then
           strResult = strResult & Delimeter & ConcatenateRng.Cells(i).Value
       End If
       Next i

       If strResult <> "" Then
           strResult = Mid(strResult, Len(Delimeter) + 1)
       End If

     Combinerows = strResult
     Exit Function
     ErrHandler:
     Combinerows = CVErr(xlErrValue)
End Function

Step6: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=combinerows(A2:A6,C2,B2:B6)

Step7: drag the AutoFill handle over other cells to  concatenate text based on unique product ID values.

concatenate text based on special criteria3

2. Video: Concatenate Text based on unique values in Another Column

This video will show how to use a formula in combination with a User defined function with VBA code to concatenate text based on unique values in another column in Excel.

3. Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Extract Email Address from Text

This post will guide you how to extract email address from a text string in Excel. How do I use a formula to extract email address in Excel. How to extract email address from text string with VBA Macro in Excel.

Assuming that you have a list of data in range B1:B5 which contain text string and you want to extract all email addresses from those text string. How to achieve it. You can use a formula or VBA Macro to achieve the result. Let’s see the below introduction.

1. Extract Email Address from Text with a Formula

To extract email address from text string in cells, you can use a formula based on the TRIM function, the RIGHT function, the SUBSTITUTE function, the LEFT function, the FIND function, the REPT function and the LEN function. Just like this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))

Select the adjacent Cell C1, and type this formula, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

exctract email address from text1

2. Extract Email Address from Text with User Defined Function

You can also write a User Defined Function with VBA Code to extract email address quickly, just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractEmailFromText(s As String) As String
    Dim AtTheRateSignSymbol As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
    
    AtTheRateSignSymbol = InStr(s, "@")
    If AtTheRateSignSymbol = 0 Then
        ExtractEmailFromText = ""
    Else
        TempStr = ""
        For i = AtTheRateSignSymbol - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        
        If TempStr = "" Then Exit Function
        
        TempStr = TempStr & "@"
        
        For i = AtTheRateSignSymbol + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    
    If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1)
    
    ExtractEmailFromText = TempStr
End Function

Step5: Type the following formula into blank cells and then press Enter key.

=ExtractEmailFromText(B1)

Type this formula into a blank cell and then press Enter key in your keyboard.

Step6: lets see the result:

exctract email address from text3

3. Video: Extract Email Address from Text in Excel

This video will demonstrate a step-by-step instruction on how to use the formula and VBA code to extract email addresses from a block of text in Excel, making it easy to manage and organize your contact information.

How to Hide or Show Objects in Excel

This post will guide you how to hide all objects in your worksheet in Excel. How do I show all hidden objects in your current worksheets with VBA Macro in Excel.

If you want to hide all objects in your current worksheet, such as: pictures, comments, charts, Textbox, etc. You can use Selection pane feature or write down an Excel VBA Macro to show or hide all objects. Let’s see the following introduction.

1. Hide or Show All Objects with Selection Pane Feature

If you want to hide or unhide all objects in the current worksheet, you can use the Selection pane feature to achieve the result. Just do the following steps:

Step1: go to HOME tab, click Find & Select command under Editing group. And select Selection Pane … menu from the drop down menu list. And the Selection pane will be shown.

hide show objects1

Step2: you should see that all of the objects are listed in the Selection pane. And You can click Hide All button to hide all objects. Or you can click Show All button to show all hidden objects.

hide show objects2
hide show objects3

Step3: if you only want to hide or show specific objects in Excel, you just need to click its eye button, or click eye button again to show it.

hide show objects4

2. Hide or Show All Objects with VBA

You can also use an Excel VBA macro to achieve the same result of hiding or showing all objects in the current worksheet. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

hide show objects5
Sub HideAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = False
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

hide show objects6

All objects in the current worksheet would be hidden.

If you want to show all hidden objects with VBA code, you can use the following VBA Macro:

Sub ShowAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = True
    Next
End Sub

3. Video: How to Hide or Show Objects in Excel

In this video, you will learn how to hide or show objects in Excel using the selection pane feature as well as VBA code.

Automatically Change Properties of All Pictures (move and size with cells)

This post will guide you on how to automatically change properties of all pictures in Excel using VBA code. How do I automatically change all pictures to move a size with cells in your worksheet with VBA macro code in excel.

You can quickly modify the properties of multiple pictures at once using VBA Macro, instead of changing them one by one. This can be a huge time saver if you have a large number of pictures in your Excel file that need to be modified.

By default, after you inserted a picture into worksheet, it will keep the default properties of move and don’t size with cells. And you need to change its properties to move and size with cells by manually in your worksheet.

1. Automatically Change Properties of All Pictures

Let’s do the following steps to automatically change properties of all pictures:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub AutoMoveAndSizeWithCells()
    Dim xPic As Picture
    For Each xPic In ActiveSheet.Pictures
        'Modify the picture properties here
        xPic.Placement = xlMoveAndSize
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

auto change properties2

You will see the all pictures are changed to move and size with cells automatically after running the above vba code.

2. Video: Automatically Change Properties of All Pictures

This video will show you how to use VBA code in Excel to automatically change properties of all pictures, allowing you to quickly modify multiple pictures at once.

How to Open Multiple Hyperlinks at Once in Excel

This post will guide you how to open multiple links at once in Excel. How do I open multiple Hyperlinks with VBA Macro in Excel 2013/2016/2019/365.

 When dealing with large data sets, it can be time-consuming and frustrating to manually click on each hyperlink to access its contents. Excel provides several built-in features to simplify this process, such as the “Follow Hyperlink” function. However, this function only works for one hyperlink at a time. In this post, we will explain how to use VBA code to automate the process of opening multiple hyperlinks quickly in Excel.

1. Open Multiple Links at Once with VBA Code

Assuming that you have a list of data which contain multiple hyperlinks and you want to open those links at once in your current worksheet. How to do it quickly. I think you have to use the VBA Macro to quickly achieve it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

open multiple hyperlinks2
Sub OpenMultipleLinks()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Range", "OpenMultipleLinks", myRange.Address, Type:=8)
    For Each oneLink In myRange.Hyperlinks
        oneLink.Follow
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

open multiple hyperlinks1

Step6: please select one range that contain hyperlinks you want to open. click ok button.

open multiple hyperlinks3

Step7: you would see that all of hyperlinks have been opened in your default browser.

2. Video: Open Multiple Hyperlinks at Once with VBA Code

This video will demonstrate a quick and easy way to open multiple hyperlinks simultaneously in Excel using VBA code, saving you time and effort while increasing productivity.

How to Count the Number of Sheets in a Workbook

This post will guide you how to count the number of sheets in a workbook in Excel 2013/2016/2019/365. How do I count the number of worksheets in a workbook with VBA Macro in Excel.

While manually counting the sheets is possible, it can be a tedious and time-consuming task, especially in larger workbooks. Fortunately, Excel provides several built-in functions and features that can help simplify this task. Additionally, for those who are comfortable with using VBA Macros, we will also explore a quick and easy method to count the number of sheets using a simple VBA macro.

1. Count the Number of Sheets with Define Name

If you want to count the number of worksheets in a given workbook in Excel, you can use the Defined Name and a Formula to achieve it. Just do the following steps:

 Step1: go to Formula tab, click Define Name command under Defined Names group, and the New Name dialog will open.

count worksheet number1

 Step1: type one defined name in the Name text box, such as: countWorksheets, and then type the formula =GET.WORKBOOK(1)&T(NOW()) into the text box of Refers to. Click Ok button.

count worksheet number2

 Step3: Type the following formula based on the COUNTA function and the INDEX function to get the number of worksheets in the current workbook. And press Enter key in your keyboard, you will get the number of worksheets in your workbook.

=COUNTA(INDEX(CountWorksheets,0))
count worksheet number3

2. Count the Number of Sheets with VBA Macro

You can also use an Excel VBA Macro to get the number of worksheets in the current workbook. Just do the following steps:

 Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

 Step2: then the “Visual Basic Editor” window will appear.

 Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

 Step4: paste the below VBA code into the code window. Then clicking “Save” button.

count worksheet number4
Sub CountWorkSheets()
    MsgBox "The number of worksheets in the current workbook is : " & Application.Sheets.Count
End Sub

 Step5: back to the current worksheet, then run the above excel macro. Click Run button.

count worksheet number5

 Step6: let’s see the result:

count worksheet number6

3. Video: Count the Number of Sheets

This video will guide you through a step-by-step process to count the number of sheets in an Excel workbook using VBA code, saving you time and effort while ensuring accuracy.

How to Save File based on Cell Value in Excel

This post will provide you with a step-by-step guide on how to save Excel workbook file with Cell value in Excel using VBA code in Excel 2013/2016/2019/365. Saving files based on specific criteria can be a tedious and time-consuming task, especially if you have to do it repeatedly. However, with VBA code, you can automate this process and save yourself a lot of time and effort.

1. Save File Based on Cell Value with VBA Code

Assuming that you have a request that save the current workbook file I am working as the value in Cell A1. For example, the value of Cell A1 is testWork, and you want to save the workbook as the name of testWork.

To Save file with Cell Value in Excel, you need to write down an Excel VBA Macro to achieve the result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

save file based on cell value1
Sub FileNameAsCellContent()
    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\test\"
    FileName = Range("A1").Value & ".xlsx"
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Let’s see the result:

save file based on cell value2

2. Video: Save File based on Cell Value in Excel

This video will demonstrate how to save a file based on a cell value in Excel using VBA code.

3. Conclusion

Now you should have a good understanding of how to use VBA code to save files based on cell values, and you’ll be able to apply this knowledge to your own Excel projects.

How to Insert Cell Content into Header or Footer in Excel

This post will guide you how to insert the contents of a paricular into the header or footer cell in your active worksheet in Excel. How do I put a cell value into header or footer in all worksheets in your active workbook using VBA Macro in Excel.

You can easily add the number of pages, the current date and time or the name of current file, including the File Full path into the header or footer in your Excel. And there is no built-in command or function to add a cell value into the header or footer. How to do it. You can use an Excel VBA Macro to insert cell Content into a paricular worksheet or all worksheet in your workbook quickly.

1. Insert cell Content into Header or Footer in a Worksheet

To put a cell value into the header or footer in your current worksheet in Excel, and you can do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

How to insert cell value into header or footer in Excel1
Sub InsertCellValueIntoHeader()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftHeader = myRange.Value
End Sub

Step5: back to the current worksheet, click on Macros button under Code group. then click Run button.

How to insert cell value into header or footer in Excel2

Step6: Select One Single Cell that you want to put its into Header or Footer. click on Ok button.

How to insert cell value into header or footer in Excel3

Step7: let’s see the last result:

How to insert cell value into header or footer in Excel4
How to insert cell value into header or footer in Excel6

If you want to add a cell value into the footer in your current worksheet, and you can use the following VBA Macro.

How to insert cell value into header or footer in Excel5
Sub InsertCellValueIntoFooter()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftFooter = myRange.Value
End Sub

If you need to add a cell value into the RightHeader or RightFooter, and you just need to change one code line(Application.ActiveSheet.PageSetup.LeftHeader or Application.ActiveSheet.PageSetup.LeftFooter ) in above VBA Function as below line:

Application.ActiveSheet.PageSetup.RigthHeader = myRange.Value

or

Application.ActiveSheet.PageSetup.RightFooter = myRange.Value

2. Insert Cell Content into Header or Footer in All Worksheets

You can also insert a cell value into the header or footer for all worksheets in your active workbook using a VBA Macro in Excel.  see belows:

How to insert cell value into header or footer in Excel7
Sub InsertCellValueIntoHeaderForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftHeader = myRange.Value
    Next
End Sub

If you need to insert cell content into footer for all worksheets, and you can use the following VBA Macro:

How to insert cell value into header or footer in Excel8
Sub InsertCellValueIntoFooterForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftFooter = myRange.Value
    Next
End Sub

3. Video: Insert Cell Content into Header or Footer in Excel

If you want to learn how to insert cell content into header or footer in Excel, this video will show you a simple and effective way using VBA code.

How to Save or Export Each Sheet to Separate CSV Files in Excel

This post will guide you how to save each worksheet to separate CSV files in your workbook in Excel. How do I save multiple or all worksheets to separate csv file in Excel 2013/2016. How to export all worksheets to separate txt files with VBA macro in Excel.

1. Export Each Sheet to Separate CSV Files

Assuming that you have a workbook that has four different worksheets, sheet1, sheet2, sheet3, and sheet4. And You want each worksheet as its own CSV file and we also want the CSV file name to take into account its Excel Source as well as the workbook where it originates. To achieve the result of exporting multiple worksheets separate csv files, you have to use VBA Macro to save all worksheets in the current workbook to separated csv files. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub
export each sheet to csv1

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

export each sheet to csv3

Step6: you would see that all worksheet has been converted to a single CSV files in the specified directory. This example will save csv file into D:\tmp\ directory. You need to change the variable “SaveToDirectory ” as you need. or you can also create a temp directory called tmp in disk D partition.

export each sheet to csv4

2. Export Each Sheet to Separate Text Files

If you want only convert each worksheet to separate text files for your active workbook, you can also use an Excel VBA Macro to achieve the result. Just using the following VBA code:

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name & ".txt"
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=xlText
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Let’s see the result:

export each sheet to csv5

3. Conclusion

By using VBA code, you can create a custom macro that will iterate through each sheet in your workbook and save it as a separate CSV file. This code can be customized to suit your specific needs and can be easily modified to include additional functionality or to save the files in a different format.

Overall, if you frequently work with large Excel workbooks that contain multiple sheets, using VBA code to save each sheet as a separate CSV file is a great way to streamline your workflow and improve your productivity.

How To Set paste Values as Default Paste in Excel

This post will guide you how to set paste values as default paste in Excel. How do I set the default paste special in Excel to paste only values when using Ctrl+v keys. when you want to copy data from a selected range of cells and then pasted it to another range with Ctrl + V keys on your keybaord, and you only want to copy cell values and without including cell formats. How to do it. you can use an Excel VBA Macro to achive the result.

1. Set Paste Values as Default Paste in Excel

If you only want to set paste values as default paste in your worksheet, and you can use an Excel VBA Macro to accomplish it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

set paste values as default paste 1
Sub SetPasteDefaultasValue()
     Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Step5: back to the current worksheet, click on Macros button under Code group, and the Macro dialog will open. then click Options button in the Macro dialog box.

set paste values as default paste2

Step6: you need to enter v key under the Shortbut key section in Macro Options dialog box. click on Ok button to take effect for changes.

set paste values as default paste3

Step7: Closing the Macro dialog box. and try to copy a cell with formatting style, and then paste it to antoher cell. You would see that only values will be copied.

set paste values as default paste4
set paste values as default paste5

2. Video: Set paste Values as Default Paste in Excel

This video will show you how to set paste values as default paste in Excel using a VBA Code.

How to Deselect Cells from Selected Range with VBA

This post will guide you how to deselect cells from a selected range of cells in your worksheet. How do I quickly deselect cells from a selected range of cells with VBA code in Excel.

Normally, if you want to deselect a cell or multiple cells from a selection, you just need to hold down the CTRL key and click on the cells you want to deselect. If you want to unselect a range of selected cells, you need to hold down the CTRL key and drag the range you want to deselect. This post will explain that how to deselect cells from a selection with VBA Macro quickly in Excel.

1. Deselect Cells from Selected Range with VBA

You can use an Excel VBA Macro to quickly achieve the same result of deselecting one or multiple cells from a selected range of cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

deselect cells from selected range1
Sub DeSelectCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range:", "DeSelectCells", myRange.Address, Type:=8)
    Set DeleteRng = Application.InputBox("select one cell or range of cells that you want to deselect", "DeSelectCells", Type:=8)
    Dim LastRange As Range
    
    For Each myCell In myRange
        If Application.Intersect(myCell, DeleteRng) Is Nothing Then
            If LastRange Is Nothing Then
                Set LastRange = myCell
            Else
                Set LastRange = Application.Union(LastRange, myCell)
            End If
        End If
    Next

    LastRange.Select

End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

deselect cells from selected range2

Step6: Select one Range that you want to deselect cells, such s:A1:D7

deselect cells from selected range3

Step7: select one cell or range of cells that you want to deselect.

deselect cells from selected range4

Step8: Let’s see the last result:

deselect cells from selected range5

2. Video: Deselect Cells from Selected Range with VBA

This video will demonstrate you how to deselect cells from a selected range of cells with a VBA Macro in your worksheet.

How to Extract First Letter from Each Word in a Cell in Excel

This post will guide you how to extract first letter from each word in a given cell in Excel. How do I extract the first letter of each word in a range of names in your current worksheet in Microsoft Excel 2013/2016. Assume that you have a range of First, Second and third names and you would like to extract the initials to a separate cell.

For example, one name called “Nigolas Kate”, and you want to extract the first letters so that the result would be “NK”.

extract first letter from word1

1. Extract First Letter from Each Word in a Cell Using User Defined Function with VBA

Since there is not built-in function to extract the first letter of each word in Excel, and you can define a user defined function by Excel VBA code to achieve the result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractFirstLetter(text) As String
    mystring = Left(text, 1)
    For i = 2 To Len(text) - 1
        If Mid(text, i, 1) = " " Then
            mystring = mystring & Mid(text, i + 1, 1)
        End If
    Next i

    ExtractFirstLetter = WorksheetFunction.Substitute(UCase(mystring), " ", "")
End Function
extract first letter from word2

Step5: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=ExtractFirstLetter(A1)
extract first letter from word3

2. Extract First Letter from Each Word in a Cell Using Formula

If you want to extract the first letter from each word in a cell in Excel, you can use a combination of the CONCATENATE, LEFT, MID and FIND functions and return them as a concatenated string.

You need to enter the following formula in a blank cell:

=CONCATENATE(LEFT(A1,1),MID(A1,FIND(" ",A1)+1,1),IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),""))

Press Enter to apply the formula.

You can then copy and paste the formula to apply it to other cells, or drag the fill handle to apply it to a range of cells.

Here’s how this formula works:

The LEFT function is used to extract the first letter of the first word in the cell.

The MID and FIND functions are used to extract the first letter of the second and subsequent words in the cell.

The CONCATENATE function is used to join the first letters of each word in the cell.

The IFERROR function is used to handle cells with only one or two words. If the cell has only one or two words, the formula returns the first letter of those words.

3. Video: Extract First Letter from Each Word in a Cell in Excel

If you want to learn how to extract the first letter from each word in a cell in Excel, you can watch this video that shows you how to use a formula or a VBA code to achieve this task.

Check If a Cell contain Image/Picture

This post will guide you how to check if a specific cell contains an image or picture in excel. Is it possible to check if a cell contains an image. This post will guide you how to define a User Defined Function to determine if an image exists in a specified cell. Or how to use VBA Macro code to check if a cell contains an image.

1. Check If a Cell contain Image Using User Defined Function

The simplest method is to create a user defined function to check if a cell contains an image file, and return 1 if image exists in cell. Otherwise, returns 0. You can follow these steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

check if a cell contain image2
Function CellImageCheck(CellToCheck As Range) As Integer
    ' Return 1 if image exists in cell, 0 if not
    Dim wShape As Shape
    For Each wShape In ActiveSheet.Shapes
        If wShape.TopLeftCell = CellToCheck Then
            CellImageCheck = 1
        Else
            CellImageCheck = 0
        End If
    Next wShape
End Function

Step5: back to the current worksheet, try to enter the below formula in Cell D6.

=CellImageCheck(B6)
check if a cell contain image1

2. Check If a Cell contain Image with VBA Macro

You can also use the following VBA code to check if a cell contains an image.  You just need to repeat the above 1-3 steps firstly, then continue to do the following steps:

Step1: paste the below VBA code into the code window. Then clicking “Save” button.

check if a cell contain image3
Sub CellImageCheck()
    Dim checkRange As Range
    Dim x As Shape
    Dim flag As Boolean
    On Error Resume Next
    Set checkRange = Application.InputBox("please enter one cell that you want to check", "CellImageCheck", Selection.Address, , , , , 8)
    If checkRange Is Nothing Then Exit Sub
        flag = False
        For Each x In ActiveSheet.Shapes
            If x.TopLeftCell.Address = checkRange.Address Then
                flag = True
            End If
        Next
    If flag Then
        MsgBox "Found an image!"
    Else
        MsgBox "No Image"
    End If
End Sub

Step2: back to the current worksheet, then run the above excel macro. Click Run button.

check if a cell contain image4

Step3: select one cell that you want to check, such as: B6

check if a cell contain image5

Step4: Let’s see the result:

check if a cell contain image6

3. Video: Check If a Cell contain Image in Excel

This video demonstrates how to use a User Defined Function with VBA Macro to check if a cell contains an image or picture in Excel.

How to remove non numeric characters from a cell

This post explains that how to remove non-numeric characters (numbers) from a text string in one cell in excel 2016/2019/365. How to remove non numeric characters from a cell containing text string with an excel formula. And how to remove alphanumeric characters from a cell with a user defined function in excel VBA.

1. Remove non numeric characters with an Excel Formula

If you want to remove non numeric characters from a text cell in excel, you can use the array formula:

{=TEXTJOIN("",TRUE,IFERROR(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0,""))}

Let’s see how the above formula works:

=ROW(INDIRECT(“1:”&LEN(B1))

The ROW function returns the below array list:

{1,2,3,4,5,6,7,8,9}

=MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)

The MID formula will return the below array:

{"e","x","c","e","l","2","0","1","6"}

=IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””)

The array returned by the above MID function add zero for each value in array. If the value is a numeric text, it will be converted to text format. If not, returns empty string. So the IFERROR function returns the below array:

{2,0,1,6}

Last, the TEXTJOIN function join the values in above array returned by the IFERROR function.

Remove non numeric characters with an Excel Formula1

2. Remove non numeric characters using VBA Code

You can create a new function to remove numeric characters from a cell that contain text string in Excel VBA. Just refer to the below steps:

Step1: open visual Basic Editor, then insert a module and name as : RemoveNonNum.

Remove non numeric characters with excel vba1

Step2: click “Insert“->”Module“, then paste the following VBA code into the window:

Step3: paste the below VBA code into the code window. Then clicking “Save” button.

remove non numeric characters from a cell1
Sub RemoveNonNum()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("select one Range that you want to remove non numeric characters", "RemoveNonNum", myRange.Address, Type:=8)
    For Each myCell In myRange
        LastString = ""
        For i = 1 To Len(myCell.Value)
            mT = Mid(myCell.Value, i, 1)
            If mT Like "[0-9]" Then
                tString = mT
            Else
                tString = ""
            End If
            LastString = LastString & tString
        Next i
        myCell.Value = LastString
    Next
End Sub

Step4: back to the current worksheet, then run the above excel macro. Click Run button.

Remove non numeric characters with excel vba4

Step5: select one Range that you want to remove non numeric characters. click Ok button.

remove non numeric characters from a cell2

Step6: Let’s see the last result:

Remove non numeric characters with excel vba4

3. Video: Remove non numeric characters in Excel

This video will demonstrate how to remove non-numeric characters in Excel using a formula or VBA code.

4. Related Formulas

  • Remove Numeric Characters from a Cell
    If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…

5. Related Functions

  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel INDIRECT  function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

How to Select Only Bold Cells in Excel

This post will guide you how to select only bold cells in a range of cells in Excel. How do I select all bold cells in a given range using VBA Macro in Microsoft Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:C5, and you want to select all cells with bold font formatting in Excel. This post will show you two methods to select all bold cells.

1. Select Only Bold Cells Using Find And Replace

If you want to select all cells with bold fond formatting, you can use Find and Replace feature to find the specific text or specific format in the worksheet, such as: cell color, bold font…etc. Just do the following steps to find and select all the bold formatting in the selected range of cells:

Step1: select the range in which contain bold cells that you want to find.

select only bold cells1

Step2: go to Home tab, click Find & Select command under Editing group. And click on Find from the Find and Select drop down list. The Find and Replace dialog will open.

select only bold cells2

Step3: click on the Options button in the Find and Replace dialog box.

select only bold cells3

Step4: click the Format button in the Find what section, and select Choose Format From Cell from the Format drop down list. And the Find Format dialog will open.

select only bold cells4

Step5: click Font tab in the Find Format dialog box, and select the Bold option in the Font Style list box, and click Ok button to go back to the Find and Replace dialog box.

select only bold cells5

Step6: click the Find All button, then all cells with the bold font formatting in the selected range of cells would be searched.

select only bold cells6

Step7: press Ctrl + C keys on your keyboard to select all results, then it will select all the cells that have the text with bold fond formatting.

select only bold cells7
select only bold cells8

2. Select Only Bold Cells Using VBA

You can also use an Excel VBA Macro to achieve the same result of selecting only bold cells in a given range of cell. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Adding Comma Character at End of Cells vba1.png

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

select only bold cells9
Sub SelectOnlyBoldCells()
    Dim myRange As Range
    Dim cell As Range
    Dim tempRange As Range
    
    Set myRange = Range("A1", "D3")
    For Each cell In myRange
        If cell.Font.Bold = True Then
            If tempRange Is Nothing Then
                Set tempRange = cell
            Else
                Set tempRange = Union(tempRange, cell)
            End If
        End If
    Next cell
     
    If Not tempRange Is Nothing Then
        tempRange.Select
    End If
    
End Sub

Note: This VBA code will use another range called tempRange, which receives the cell reference with the bold fond formatting. And the UNION function is used to concatenate the addresses together. After the procedure finished, the range tempRange is selected.

You need to change the value of myRange variable as you need.

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

select only bold cells10

Step6: Let’s see the result:

select only bold cells8

3. Video: How to Select Only Bold Cells in Excel

In this video, you will learn how to select only bold cells in Excel using find and replace as well as VBA code.

How to Get the Position of the nth Occurrence of a Character in a Cell

In the previous post ,we talked that how to get the position of the last occurrence of a character in a cell, and sometimes, you may be want to know the position of the 2th, 3th or nth occurrence of a character in a text string in excel. and this post will guide you how to find the 2th, 3th, or nth occurrence of a character in a text string using excel formula and use defined function.

1. Get the Position of the Nth Occurrence of a Character using Excel Formula

If you want to get the position of the nth occurrence of a character using a excel formula, you can use the FIND function in combination with the SUBSTITUTE function. For example, to get the position of the 2th occurrence of the character “e” in Cell B1, you can create the following formula:

=FIND("#",SUBSTITUTE(B1,"e","#",2))

The SUBSTITUTE function will replace the 2th occurrence of the character “e” with the hash character. So it returns another text string as “exc#l”.

Get the position of the nth using excel formula1

The FIND function look up the hash character in the text string returned by the SUBSTITUTE function, and it returns the position of the first hash character in string “exc#l”. And it should be the position of the 2th occurrence of the character “e” in Cell B1.

Get the position of the nth using excel formula2

2. Get the Position of the Nth Occurrence of a Character using User Defined Function

You can also create a new user defined function to get the position of the nth occurrence of a specific character or string in Excel VBA:

Step1: click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

Step1: then the “Visual Basic Editor” window will appear.

Step2: click “Insert” ->”Module” to create a new module named as: getNthPosition

Get the position of the nth using excel vba1

Step3: paste the below VBA code into the code window. Then clicking “Save” button.

Get the position of the nth using excel vba1
Function getNthPosition(sFindValue As String, sTextString As String, N As Integer) As Integer
    Dim i As Integer
    Application.Volatile
    getNthPosition = 0
    For i = 1 To N
            getNthPosition = InStr(getNthPosition + 1, sTextString, sFindValue)
        If getNthPosition = 0 Then Exit For
    Next
End Function

Step4: back to the current workbook, then enter the below formula in Cell C1:

=getNthPosition("e",B1,2)
Get the position of the nth using excel vba4

3. Video: Get the Position of the Nth Occurrence of a Character

In this video, you will learn a formula and VBA code that can be used to get the position of the Nth occurrence of a character in a string.

4. Related Formulas

  • Get the position of Last Occurrence of a value in a column
    If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
  •  Get the position of Last Occurrence of a character or string in a cell
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula.…
  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Get the First Row Number in a Range
    If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
  •  Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

5. Related Functions

  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string. The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function. = FIND(find_text, within_text,[start_num])…

How to Set Specific Cells as Read Only/Locked in Excel

This post will guide you on how to set specific cells as read-only or locked using Protect Sheet feature and vba code in Microsoft Excel 2013/2016/2019/365.

In our daily work, in case of protect our worksheet from being edit by others, we often add a password for opening worksheet. But sometimes, the worksheet is open for all or shared with your partners, we just want to set some important cells as read only in case of preventing others to edit them in worksheet, for example we need to set some cells with formulas or salaries as read only in payroll, how can we do? This article will help you to make your specific cells as read only.

1. Lock Specific Cells in Worksheet by Format Cells Settings

Prepare a table with ID, Name, Score three columns. And we want to make some cells in this table cannot be editable by others.

A simple table for demonstration in this article:

Set Specific Cells as Read Only 1

Step1: Press Ctrl+A to select the whole worksheet. You can also click on the arrow Set Specific Cells as Read Only 2 arrow button to select whole cells on current worksheet, see screenshot below.

Set Specific Cells as Read Only 3

Step2: On worksheet anywhere, right click to load menu, select Format Cells. You can also load Format Cells window by press Ctrl+1.

Set Specific Cells as Read Only 4

Step3: Format Cells window is loaded properly.

Set Specific Cells as Read Only 5

Step4: Click on Protection, there are two options, Locked and Hidden, Locked option is checked by default. Uncheck Locked option, then click OK.

Set Specific Cells as Read Only 6

Notes:

a. You can see the comments under the option that locked or hidden options only take effective once protect the worksheet is enabled. So, we need to protect the worksheet then.

b. If we don’t uncheck Locked option first, and directly protect worksheet by password, then the whole worksheet will be locked. So this step is to make sure that only the selected range will be locked, please see the following step.

Step5: Select the range you want to make them as Read Only, for example select A2:C4. Right click to load menu, select Format Cells again.

Set Specific Cells as Read Only 7

Step6: This time, enter Protection tab, check on the Locked option, then click OK.

Set Specific Cells as Read Only 8

Step7: Click Review->Protect Sheet.

Set Specific Cells as Read Only 9

Step8: In Protect Sheet window, “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are checked by default. Enter password in textbox and click OK, re-enter and confirm password again, then click OK.

Enter password:

Set Specific Cells as Read Only 10

Confirm password:

Set Specific Cells as Read Only 11

Step9: Try to edit the table. Verify that below error message pops up.

Set Specific Cells as Read Only 12

Step10: Click A3. Verify that user still can edit it due to it is not locked.

Set Specific Cells as Read Only 13

Step11: If we want edit the locked cells, please click on Review->Unprotect Sheet. Unprotect Sheet pops up.

Set Specific Cells as Read Only 14

Step12: Enter the password. Click OK. Now you can edit specific locked cells normally again.

Set Specific Cells as Read Only 15

2. Lock Specific Cells in Worksheet by VBA

Step1: Right click on Sheet1 to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

How to Prevent Users from Adding New Worksheet 6

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

How to Prevent Users from Adding New Worksheet 7

Step2: In Microsoft Visual Basic for Applications window, click on Sheet1, enter below code:

Set Specific Cells as Read Only 16
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
        If Target.Row = 2 Or Target.Row = 3 Or Target.Row = 4 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
            MsgBox Cells(Target.Row, Target.Column).Address & " You cannot edit the cell!", _vbInformation, "Error Message"
        End If
    End If
End Sub

Comments:

a. In above script, Target.Column and Target.Row define the range will be locked. In this case, column 1,2,3 (A, B, C) and row 2,3,4 are included, that means range A2:C4 is selected and locked.

b. “You cannot edit the cell!” is the message pops up when user clicking on locked cell. “Error Message” is the header for pops up error message. You can replace them.

Step3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step4: Try to edit the locked range A2:C4. Below error message pops up.

Set Specific Cells as Read Only 17

Step5: Try to edit A1 which is not locked. Verify that user still can edit A1 properly. User can also edit other unlocked cells as well.

Set Specific Cells as Read Only 18

3. Video: Set Specific Cells as Read Only/Locked in Excel

This Video will show yo how to set specific cells as red-only or locked in Excel using Protect Sheet feature and VBA Code.

4. SAMPLE Files

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Conclusion

Excel provides users with the ability to protect their worksheets and workbooks to prevent unauthorized changes. By default, all cells in a worksheet are editable, but you can restrict access to specific cells to maintain data integrity and prevent accidental changes. In this guide, you can set specific cells as read-only or locked, and ensure that only authorized users can modify them.