Hello All. Could someone show me a macro that will remove the word "CRISP" from all cells that have over 25 characters in Row A. If the cell has 25 characters or less than then it does nothing. A space would be considered a character.
Hello All. Could someone show me a macro that will remove the word "CRISP" from all cells that have over 25 characters in Row A. If the cell has 25 characters or less than then it does nothing. A space would be considered a character.
![]()
Sub RunMe() Dim c As Range For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = Replace(c, "CRISP", "") End If Next c End Sub
Helps?
![]()
Sub test() [a1:a10000] = [if(a1:a10000<>"",if(len(a1:a10000)>25,trim(substitute(a1:a10000,"CRISP","")),a1:a10000),"")] End Sub
How would the below macro look If I wanted it to function like so. Remove all cell contents and replace with "Hello my name is" if the character count is over 25?
![]()
Sub RunMe() Dim c As Range For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = Replace(c, "CRISP", "") End If Next c End Sub
Last edited by wonderd; 02-19-2015 at 06:31 PM.
Bump. Sorry marked the thread as unsolved. Have a similar question.
![]()
Sub RunMe() Dim c As Range Dim strWhat As String, strReplacement As String strWhat = "CRISP" strReplacement = "Hello my name is " For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = Replace(c, strWhat, strReplacement) End If Next c End Sub
![]()
Sub RunMe() Dim c As Range Dim strWhat As String, strReplacement As String strWhat = "CRISP" strReplacement = "Hello my name is " For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = Replace(c, strWhat, strReplacement) End If Next c End Sub
Actually I am not looking to use the word CRISP at all. The function should just be if any cells in row A has over 25 characters then delete all content in the cell and replace the cell content to "Hello my name is"
Last edited by wonderd; 02-19-2015 at 07:59 PM.
So instead of replace the word CHRISP. It would replace all content in the cell with over 25 characters with with "Hello my name is" I am trying to figure out how to do it with the code below but can not figure it out.
![]()
Sub RunMe() Dim c As Range For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = Replace(c, "CRISP", "") End If Next c End Sub
![]()
Sub RunMe() Dim c As Range For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) If Len(c) > 25 Then c = "Hello my name is I will try to get my initial post right the first time" End If Next c End Sub
I appreciate the help but not the sarcasm. My initial post was right but I did not want to create another thread for a similar question...I needed both macros. I guess I wont try to be so friendly next time, maybe that was my mistake.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks