Welcome to the Microsoft Excel Forum community. Make sure you adhere to the Forum Rules before participating.
Questions that contain attachments such as an image or an Excel file get the most engagement. To attach your Excel file you can post a link or use the media uploader directly.
How to keep one type of number in a column and delete the other type?
As a teacher, having successfully cataloged my class library in a spreadsheet, I now face the challenge of uploading it to a website. However, the website recognizes books solely based on the 978 number, and I need a solution to remove any other numbers in the spreadsheet column while retaining only the 978 numbers. Is there a method or script to achieve this task effectively?
As a teacher, having successfully cataloged my class library in a spreadsheet, I now face the challenge of uploading it to a website. However, the website recognizes books solely based on the 978 number, and I need a solution to remove any other numbers in the spreadsheet column while retaining only the 978 numbers. Is there a method or script to achieve this task effectively?
Uploaded files:You can use VBA code to solve this problem. Copy and paste then press the shortcut ALT+F8 to run the Macro
Sub UniqueRemoveNumberAfter978()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cellValue As String
Dim result As String
' Set the worksheet (modify the sheet name if needed)
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each cell in column A
For i = 1 To lastRow
cellValue = Trim(ws.Cells(i, 1).Value) ' Trim to remove extra spaces
' Check if "978" is present in the cell
If InStr(cellValue, "978") > 0 Then
' If "978" is in the first position, keep only the part of the string that starts with "978"
If InStr(cellValue, "978") = 1 Then
result = "978" & Mid(cellValue, Len("978") + 1)
End If
End If
' Remove the number after "978" (separated by a space) in column B
If InStr(result, "978") >
You can use VBA code to solve this problem. Copy and paste then press the shortcut ALT+F8 to run the Macro
Sub UniqueRemoveNumberAfter978()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cellValue As String
Dim result As String
' Set the worksheet (modify the sheet name if needed)
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each cell in column A
For i = 1 To lastRow
cellValue = Trim(ws.Cells(i, 1).Value) ' Trim to remove extra spaces
' Check if "978" is present in the cell
If InStr(cellValue, "978") > 0 Then
' If "978" is in the first position, keep only the part of the string that starts with "978"
If InStr(cellValue, "978") = 1 Then
result = "978" & Mid(cellValue, Len("978") + 1)
End If
End If
' Remove the number after "978" (separated by a space) in column B
If InStr(result, "978") >
ByAmos GikundaMar 6, 2024
