MS Office 365

 

TO Update Edited Date and Time Row by Row :- 

To automatically update an Excel sheet with the current date and time whenever a specific row is updated, you can use a combination of VBA (Visual Basic for Applications) scripting within Excel.

Here’s a step-by-step guide:

1. Open the Excel File

• Open the Excel file you are working on.

2. Open the VBA Editor

• Press Alt + F11 to open the VBA Editor.

3. Insert VBA Code

• In the VBA editor, on the left side, find the name of your workbook (usually called VBAProject (YourWorkbookName.xlsx)).
• Right-click on ThisWorkbook or the specific sheet name where you want the date and time to appear and select View Code.

4. Add the Following VBA Code

Copy and paste this code into the editor:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UpdateColumn As Integer
    UpdateColumn = 7 ' Column where date and time will be added (Adjust the number to the correct column number)

    If Target.Column < UpdateColumn Then ' Adjust if necessary
        Cells(Target.Row, UpdateColumn).Value = Now
        Cells(Target.Row, UpdateColumn).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
    End If
End Sub

5. Modify for Your Worksheet

• Replace UpdateColumn = 7 with the correct column number where you want the date and time to be added (for example, if it's in column G, the value is 7).
• The Now function adds the current date and time.
• This script checks if a value in any cell before the date/time column is changed and then updates the corresponding row with the current date and time.

6. Save and Close VBA Editor

• After you have pasted the code, save the workbook (it will ask you to save it as a macro-enabled workbook).
• Close the VBA editor by pressing Alt + Q.

7. Test the Workbook

• Now, whenever you edit any cell in the specific row (before the date/time column), the last column will automatically update with the current date and time.

This method will timestamp your rows whenever there is an update in the worksheet.

Extract Specific Data from a Cell :-

To extract the middle content (in your case, "VIL0030") from a string like "Dell Vostro 15/VIL0030/4824L2" in Excel, you can use this formula:

excel

 

=TEXTSPLIT(A1, "/")(1,2)

However, if you're using a version of Excel that doesn't support TEXTSPLIT, use this alternative with MID, FIND, and SEARCH:

excel

 

=MID(A1, FIND("/", A1) + 1, FIND("/", A1, FIND("/", A1) + 1) - FIND("/", A1) - 1)

Explanation:

• FIND("/", A1) — finds the first /
• FIND("/", A1, FIND("/", A1) + 1) — finds the second /
• MID then extracts the text between those two positions

Apply this formula to the first cell (e.g., A1), then drag it down for the rest of the 203 cells.