Removing partial data from Excel Cells

Question

I am trying to remove part of the contents of a cell.  I could have upwards of 20000 rows and I need to remove the text below.

for example

Row 1     ABCDEFGHIJK Q/A

Row 2     ABCDEFG  Q/T

Row 3     ABCDFT Q/B/X

 What I want to do is REMOVE anything to the right of the Q/

Is there any way to do this with a Macro?

Answer 1

Sure, the code below should do it for you, just change the "Const" values to match up with your worksheet and its layout.

NOTE: this code is going to leave that space just before the Q as part of what's left behind.  If you want to get rid of that then change this line below

  Const findInData = "Q/"

to become

  Const findInData = " Q/"

Sub RemoveQSlash()

  'change these Const values as needed

  'name of the sheet with the data on it

  Const dataWSName = "Sheet1"

  'column with the data in it

  Const dataColID = "A"

  'first row with an entry to examine

  Const dataFirstRow = 1 ' might be 2 if 1 has a label

  'what we are looking for

  Const findInData = "Q/"

  Dim dataWS As Worksheet

  Dim dataRange As Range

  Dim anyDataCell As Range

  Dim foundPos As Integer

  Set dataWS = ThisWorkbook.Worksheets(dataWSName)

  Set dataRange = dataWS.Range(dataColID & dataFirstRow & ":" _

   & dataWS.Range(dataColID & Rows.Count).End(xlUp).Address)

  Application.ScreenUpdating = False

  For Each anyDataCell In dataRange

    foundPos = InStr(UCase(anyDataCell), findInData)

    If foundPos > 0 Then

      anyDataCell = Left(anyDataCell, foundPos - 1)

    End If

  Next

  Set dataRange = Nothing

  Set dataWS = Nothing

  MsgBox "Task Finished", vbOKOnly + vbInformation, "Job Done"

End Sub

Answer 2

Hi,

Try this

1. Select the range of data and press Ctrl+H
2. In the Find What box, type Q/*
3. In the Replace with box, type Q/
4. Click on Replace All

Hope this helps.

Answer 3

Thanks for such a quick reply.  This macro worked perfectly for what I needed to do.  Thanks!

Answer 4

Assuming your string are in column A then give this formula at B1 and copy it down.

=LEFT(A1,SEARCH(" Q/*",A1)+2)