|
Delete Only the Text from Cells
This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cells. That means that if you are trying to get letters out of part numbers (etc.) and you run this macro, the numbers and other characters will remain but all text will be gone.
This is a great macro for scrubbing or cleaning large amounts of data. This will remove capitalized and lowercase text; it will remove all text from the cells.
This macro works on a selection of cells. That means that it will only remove text from cells which you select. This way it is easier to control what is deleted.
Where to install the macro: Module
Delete Text Only from Cells
Sub Remove_Text_From_Cells()
‘Removes text from cells.
‘Will not remove any characters other than text
Dim rngCell As Range
Dim intChar As Integer
Dim strCheckString As String
Dim strCheckChar As String
Dim intCheckChar As Integer
Dim strClean As String
For Each rngCell In Selection
strCheckString = rngCell.Value
strClean = ""
For intChar = 1 To Len(strCheckString)
strCheckChar = Mid(strCheckString, intChar, 1)
intCheckChar = Asc(strCheckChar)
Select Case intCheckChar
Case 65 To 90
Case 97 To 122
Case 128 To 151
Case 153 To 154
Case 159 To 165
Case Else
strClean = strClean & strCheckChar
End Select
Next intChar
rngCell.Value = strClean
Next rngCell
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hey guys,
I have some data that I exported and I have excel parsing it. I am using Text2Columns, but what happens is some employees have there first, middle, and last name in the report. This makes everything on that row shift over by 1 cell to the right. I am wanting to have excel look at one column, find the cells that contain text (A-Z, a-z, etc), delete those cells and shift the cells left. Is that possible?
This forum has been critical to me getting a HUGE project completed for my job. I would like to think everyone who has helped me so far. Thank you!
I have long text lists that require certain values to be removed but it may not be the entire value of the cell. So As I'm searching for let's say ".ht" I can replace all values with "%%%%%%%%%%%%%%%%%%" but I want a macro that says any cell containing "%%%%" gets deleted (the whole cell not just the %'s). This should be possible, no? How would I perform such a task.
"Find" finds every cell and "Replace" with null deletes everything in that
cell!
I need to delete ALL black text cells, and only keep colored ones.
http://www.putfile.com/pic.php?img=5810668
Please ignore the circles, because I now want to delete ALL black text fields.
In addition, I need the cells shifted up. So it should do the same thing as Right Click --> Delete --> Shift Cells Up --> OK.
Is there any way I can do that with a command or macro? And if so, can you PLEASE tell me how to do that real quick?
Thanks so much, this would save me hours everyday.
Hi! I'm halfway there...
I'm writing a macro that will look through the sheet for the words "Subtotal" and "Total metals" and look at the cells below them. If the cells below them are blank, then I want to delete the current blank cell and two cells to the right of the current blank cell.
My current code won't allow to be run twice on the same file.
Is there any way to write this where the action would be this:
If the cells below "Subtotal" and "Total metals" are blank, then delete the current cell and two cells to the right.
Thanks!
Code:
Sub Metals()
Dim Found As Range
Dim c As Integer
'This will find the cell below "Subtotal"; blank current and 2 cells to the right
Set Found = Cells.Find(What:="Subtotal", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(Found.Offset(1, 0), Found.Offset(1, 0)).Select
If ActiveCell = "" Then
Range(Found.Offset(1, 0), Found.Offset(1, 3)).Delete xlShiftUp
End If
'This will find the cell below "Total metals"; blank current and 2 cells to the right
Set Found = Cells.Find(What:="Total metals", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Found = "" Then
Else
Range(Found.Offset(1, 0), Found.Offset(1, 0)).Select
If ActiveCell = "" Then
Range(Found.Offset(1, 0), Found.Offset(1, 3)).Delete xlShiftUp
End If
End If
End Sub
Hello,
I have a problem that I hope you guys can help me with.
I will for simplicity make the problem a bit basic and then I hope I by myself can transform into the bigger picture :-)
I have two columns. Column A is the "maintask" and column B is subtasks. Usually a maintask have between 1-5 subtasks.
I have made the sheet so that maintasks in column A merges so it fits with the number of subtask, i.e. if I have 3 sub tasks in B1:B3 then the maintask title is merged to fit cells A1:A3.
This is all very nice, and I have no problem deleting subtasks IF they arent in the top cell, i.e. B1. The macro is so that it deletes the entire row, so when deleting B1's subtask, then entire maintask is also deleted since the title of merged cells is stored in top cell, i.e. A1.
How can I delete subtasks in the top cell without it deleting the title in the merged cells that contains the maintask title?
Please remember that I have simplified the problem :-)
Hope you guys can help!
Br
Fred
Hi
I would like someone to help me with a formula to combine multiple text values from cells in a row like.
AB, AM, AZ, BB, BY
and also to remove text value found in FL (same row) and output the total value into FZ.
ALL THESE VALUES ARE A PART OF SINGLE ROW
so basically FZ= (AB + AM + AZ + BB + BY) -FL
I have tried the concentrate fn, but some values are separated with comma, so the output combines some values from multiple cells into one incomprehensible word
Sample Workbook Attached:
Final Value in M should be A+E+G+I and subtract K from total
So M should look like First, Second, Fourth, Fifth, Nine, Eleven, Thirteen, Seventeen
Many thanks for this...
Columns A and B retrieve first and last names from another sheet using a formula. I set up one InputBox for First name and one for Last name. Next, I need to search each row in each sheet for that first and last name. When rows with both the entered first and last name are found, I need nearby cells in that same row to be cleared of text. Here is what almost works:
VB:
Sub DeleteEmployee()
Dim Sht As Worksheet
Dim First As String
Dim Last As String
First = InputBox("Enter Employee's First Name (Case Sensitive):")
Last = InputBox("Enter Employee's Last Name (Case Sensitive):")
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
For Each Sht In Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
With Sht
.Activate
Cells.Select
End With
BeginRow = 4
EndRow = 183
For R = BeginRow To EndRow
[COLOR=red]If Range("A" & R).Text = First And Range("B" & R).Text = Last Then[/COLOR]
Range("D" & R & ":AI" & R).ClearContents
End If
Next R
Range("D4").Select
Next Sht
Application.ScreenUpdating = True
ThisSheet.Select
Set ThisSheet = Nothing
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Something is wrong with the red part. When I enter names in the InputBox, they are not matching with the text in the searched cell. Any suggestions?
Hi,
Would appreciate any help :D
I receive thousands of raw data every week as shown in the excel sheet {raw data}
I would like to use a macro that could automatically sort the raw data like the format in the excel sheet {template}
For example:
Looking at the raw data and compare with the template, i would have to delete the whole columns that have (BU_CODE , EAN_CODE, SUPPLIER_CODE, SUPPLIER_NAME , BRAND_NAME, and SUB_CATEGORY_CODE) and add in columns and name it as Principal, Cateogry , Brand and range).
The sequence has to similar to the excel sheet {template}
Thanks in advanced!
Hi,
Would appreciate any help :D
I receive thousands of raw data every week as shown in the excel sheet {raw data}
I would like to use a macro that could automatically sort the raw data like the format in the excel sheet {template}
For example:
Looking at the raw data and compare with the template, i would have to delete the whole columns that have (BU_CODE , EAN_CODE, SUPPLIER_CODE, SUPPLIER_NAME , BRAND_NAME, and SUB_CATEGORY_CODE) and add in columns and name it as Principal, Cateogry , Brand and range).
The sequence has to similar to the excel sheet {template}
Thanks in advanced!
|
|
