Need to exclude a word (or number of characters) in my code

regex exclude character
regex exclude word
regex exclude pattern from result
regex exclude character from group
regex exclude string
regex exclude special characters
regex exclude numbers
regex exclude string in the middle

I am trying to create a Module that will format an excel spreadsheet for my team at work. There is one column that will contain the word "CPT" and various CPT codes with descriptions. I need to delete all text (CPT description) after the 5 digit CPT code but alsp keep the word CPT in other cells.

For example: Column S, Row 6 contains only the word "CPT" (not in quotations) Then Column S, Row 7 contains the text "99217 Observation Care Discharge"

This setup repeats several times throughout Column S.

I would like for Row 6 to stay the same as it is ("CPT") but in Row 7 i only want to keep "99217"

Unfortunately, this is not possible to do by hand as there are several people who will need this macro and our spreadsheets can have this wording repeated hundreds of times in this column with different CPT codes and descriptions.

I have tried various If/Then statements, If/Then/Else

Sub CPTcolumn()
Dim celltxt As String
celltxt = ActiveSheet.Range("S6" & Rows.Count).End(xlUp).Text
Dim LR As Long, i As Long
LR = Range("S6" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    If InStr(1, celltxt, "CPT") Then
Next i

Else
    With Range("S6" & i)
    .Value = Left(.Value, InStr(.Value, " "))
End With
Next i

End If
End Sub

When i try to run it I get Various "Compile Errors"

Lesson 4: Excluding specific characters, might only want to match phone numbers that are not from the area code 650. For example, the pattern [^abc] will match any single character except for the  When using WORD 2010 to reveal the characters with spaces, I often want to exclude text boxes, headers and footers from that character count. WORD 2010 shows a box to tick to either include or exclude that specific information from the count.

Your first error will occur here:

ActiveSheet.Range("S6" & Rows.Count).End(xlUp).Text

Because you're trying to retrieve text from the last used range starting .End(xlUp) at Range("S61048576"), which is roughly 58 times the row limit in Excel. You might change Range("S6" & Rows.Count) to Range("S" & Rows.Count)

Your second error will occur here:

LR = Range("S6" & Rows.Count).End(xlUp).Row

Which will be the same error.

The third error will occur here:

For i = 1 To LR
    If InStr(1, celltxt, "CPT") Then
Next i

You cannot nest half of an If-End If block in a For-Next loop, or vice-versa and you've done both. If you want to iterate and perform an If-End If each iteration, you need to contain the If-End If within the For-Next like

For i = 1 To LR
    If InStr(1, celltxt, "CPT") Then
    'Is the purpose here to do nothing???
    Else
        With Range("S" & i)
            .Value = Left(.Value, InStr(.Value, " "))
        End With
    End If
Next i

EDIT:

For technical accuracy, your first error would actually be your broken up For-Next and If-End If, as you wouldn't even be able to compile to execute the code to run into the other two errors.

Narrative by Numbers: How to Tell Powerful and Purposeful Stories , It's one of the reasons brand names are so distinctive and so powerful around the word used in other contexts – as in the case of “Tide” vs “tide” – you need to be within xx characters or words of the target word; always exclude if it's within yy to identify terrorists based on keywords or constantly changing code words. One user provided a working solution for the Y column, so I now need the X values. The problem that I'm running up against is that their solution for Y counts/includes the Y's coordinates when applied to X. This does not work for my purposes. So, my present situation is---What would it require to exclude all characters from Y forward? TYIA. Best.

You can simply use the Mid function in the worksheet. As I understood from your question that you need to separate numbers and put them in other cells, is this true? To do this, you can write this function in cell R6 like this =Mid(S6,1,5) Then press enter and drag the function down and you will find that all the cells containing numbers and texts have been retained numbers in them

"The 325": The Supreme Court and Our Criminal Code and Ors., shall not be required to answer, any question tending to show that he has as to involve imputations on the character of the prosecutor or the witnesses for the that the word "may" did not confer a discretion on the trial judge to exclude the  LEFT(A5) grabs the single space code in the formula using LEFT & CODE function and giving as input to char function to replace it with an empty string. As you can see the value is cleaned in both the cases whether it is single space or any other character.

Finding and replacing characters using wildcards, The following is a list of the characters that have a special meaning in wildcard There is no limit to the amount of text that the * character might match. Even ^​19 (the field code character code) doesn't work in wildcard searches – this seems​  Here denotes any word character and denotes any non-word character. Note. This is a regex only challenge. You are not required to write any code. You only have to fill the regex pattern in the blank (_____).

About regular expressions (regex) - Analytics Help, In the context of Analytics, regular expressions are specific sequences of you could use a regular expression to exclude any data from the entire range of IP Matches the adjacent characters at the beginning of a string, ^10 matches 10, 100 Use the backslash (\) to escape regex metacharacters when you need those  Working from the inside out, LEN calculates the total length of each value. For D6 (736 miles) the total length is 9. To get the number of characters to extract, we subtract 6, which the length of " miles", including the space character. The result is 3, which is fed to LEFT as the number of characters to extract.

Refine web searches - Google Search Help, You can use symbols or words in your search to make your search results more precise. Google Search Don't put spaces between the symbol or word and your search term. A search for Exclude words from your search. Put - in front of a word you want to leave out. For example Search within a range of numbers. 1. Select the range which cells you need to remove dashes, and then click Kutools > Text > Remove Characters, see screenshot: 2. In the Remove Characters dialog box, check Custom option, and type “ - ”character into the text box, then you can preview the results from the Preview Pane.

Comments
  • You should also report the precise error, and the line. One problem with your code: "S6" & Rows.Count -->S61048576 which is not a valid address. Perhaps you mean "S" & Rows.Count.
  • Thank you for pointing that out. I just changed it to "S" & Rows.Count throughout the code. This time I got a Compile Error: Next without For Line 9
  • That's because you are trying to break up your For...Next with the If...Then...Else
  • Oh my goodness thank you so so so so so much!!! It worked perfectly! I have been working on this (just this one issue in the code) for 2 days. I cannot thank you enough.
  • So my boss changed it up, she needs all text after the first 5 characters to be moved to another cell in column X. Is that even possible?
  • If you understand how the above algorithm is working, it should be a trivial change, involving string functions (probably Mid) and the .Offset property. So first, go through the code step-by-step and make sure you understand each line. Post back if you don't understand something. VBA Help will be useful, also.
  • I have corrected the "S6" problem. For the line -- If Instr(1, celltxt, "CPT" Then i am trying to tell the code that if the cell contains the word "CPT" to do nothing and move on to the next cell.
  • Then just test for If InStr(1, celltxt, "CPT") > 0 Then and remove the Else entirely. InStr will return 0 if it finds nothing in the string.
  • No the cells with the numbers and text need to only contain the numbers. So the end result I am looking for is EX: Row 6 will have "CPT" and Row 7 will have only "99217" the rest of the text in Row 7 can be removed altogether.
  • @Hal I tried this function as the way you want it to be and the result is exactly what you want to reach , the value in S6 cell will remain "CPT" and the value in S7 cell will be "99217" but the result in the cell which you write function in it