Delete text line with specific word from multiline cell

[kurdjup]

Hi folks,

I have excel file with a lot of cells with multiline text. Is there a way to delete line that contains a certain word?

e.g.
I want to find all lines that contains PH and remove that lines

this:
Colour protection from fading.
PH 4.5 technology.
shampoo + acidic sealer.

to became:
Colour protection from fading.
Shampoo + acidic sealer.

Thank you for your time.

[paulked]

Hi and welcome to the forum.

Is it always PH or sometimes ph, Ph or pH?
Is it always at the start of the line or, for example 'The PH 4.5 technology was used.'
Are the lines always in the same column?

[paulked]

Make a back-up copy of your sheet before you run any code which deletes stuff (like this code!)

This will delete anything starting with ph in column A.



Sub DelPH()
Dim i As Long, txt As String
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
txt = Left(Cells(i, 1), 2)
If txt = "PH" Or txt = "pH" Or txt = "ph" Or txt = "Ph" Then
Rows(i).Delete
End If
Next
End Sub

[kurdjup]

Hi paulked.

It is always PH and it is always in the same column.
Is not always at the start of the line, sometimes it's in the middle of a sentence.

[paulked]

Sub DelPH()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If InStr(Cells(i, 1), "PH") Then
Rows(i).Delete
End If
Next
End Sub

[kurdjup]

Thanks paulked, it works but script delete entire row.
Is it possible to delete only one line of text (which contain PH) inside of cell, not entire row?

[paulked]

Sorry, I misunderstood you.

It is possible but I'm off out shortly.

Can you post some typical data or the file and I'll have a look when I get back.

Thanks

[paulked]

Sub DelPH()
Dim i As Long, j&, txtOld() As String, txtNew$
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
txtNew = ""
If InStr(Cells(i, 1), "PH") Then
txtOld = Split(Cells(i, 1), Chr(10))
For j = LBound(txtOld) To UBound(txtOld)
If InStr(txtOld(j), "PH") = 0 Then
txtNew = txtNew & txtOld(j) & Chr(10)
End If
Next
Cells(i, 1) = txtNew
End If
Next
End Sub


Out the door now, not ignoring you!!

[kurdjup]

It works now! :)

paulked thank you very much for your time!

[paulked]

You're welcome :thumb

Just to tidy up, add the line in red. It will delete the last Chr(10) from the string so you don't have an empty line at the bottom of the cell.



Sub DelPH()
Dim i As Long, j As Long, txtOld() As String, txtNew As String
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
txtNew = ""
If InStr(Cells(i, 1), "PH") Then
txtOld = Split(Cells(i, 1), Chr(10))
For j = LBound(txtOld) To UBound(txtOld)
If InStr(txtOld(j), "PH") = 0 Then
txtNew = txtNew & txtOld(j) & Chr(10)
End If
Next
txtNew = Left(txtNew, (Len(txtNew) - 1))
Cells(i, 1) = txtNew
End If
Next
End Sub

[kurdjup]

Thanks again :) :thumb
I managed to solve that minor "problem" :grinhalo: but certainly a more elegant solution is one script.

[romsky00]

Thanks again :) :thumb
I managed to solve that minor "problem" :grinhalo: but certainly a more elegant solution is one script.


Hi, I know that this is quite an old thread, I need this now. Exactly the same BUT my requirement is that, entire row WILL NOT BE DELETED. Example in Column G, columns have this:

HERR-101, HERG-789, HERT-789

Only the "HERR-101" will be deleted and will retain "HERG-789", "HERT-789". The number after "HERR" is also changing so I am thinking of just use "HERR-".

Please help. Thank you in advance!

[Aussiebear]

Please start a new thread.