How to delete string from cells if it contains the letter x ?

[Tardisgx]

9X60X11

I have variations of dimensions such as the string above in my cells. Their position is not consistent. The only consistent rule is that the word I want to be rid off in all my cells contains the letter x, but how?

 

[Yongle]

Hopefully, this formula will return only those values that you want

=IF(ISBLANK(A1),"",IFERROR(IF(SEARCH("x",A1)>0,""),A1))

 

[Yongle]

or to return the values containing X

=IF(ISBLANK(A1),"",IFERROR(IF(SEARCH("x",A1)>0,A1),""))

 

[Tardisgx]

Hi the formula does not operate as needed. At the moment the entire cell is cleared; I want to delete any word containing the letter x

A1 Red Bear Table 60x31x40

A1 Red Bear Table

 

[Yongle]

You forgot to say that the other text in the cell could also contain "X" - a different approach is needed
Please provide 20 typical but varied examples so that anyone looking at this understands the variety of strings to remove (and position within cell text), so that a proposed solution can be adequately tested
thank you

 

[Tardisgx]

You forgot to say that the other text in the cell could also contain "X"

https://imgur.com/a/GKOzbTl

As I said
"At the moment the entire cell is cleared"
&
"The only consistent rule is that the word I want to be rid off in all my cells contains the letter x"

I do not know why you believe in the formula over my feedback; so I've screenshotted proof; the formula is also active in B25. I do appreciate your input.

 

[Yongle]

I do not know why you believe in the formula over my feedback

I did not say that - please read my reply again

"The only consistent rule is that the word I want to be rid off in all my cells contains the letter x"

Please reconsider your rule
Is it impossible for the other text in your cell to contain "X"? (Faux Leather Chair 20X20X20, Luxury Table 30X30X30 etc)

If you paste the sample data requested in post#5 into your reply, I will look at it later
(not a screenshot - I want to paste it into a test workbook)

thank you

 

[jtakw]

"The only consistent rule is that the word I want to be rid off in all my cells contains the letter x"

I do not know why you believe in the formula over my feedback; so I've screenshotted proof; the formula is also active in B25. I do appreciate your input.

Hi,

Yongle is just trying to Clarify the different types/formats of strings you have.
And as pointed out, I also don't believe we can use the letter X as a rule, I'm fairly certain some of your Text strings will contain 1 or more X other than the Part you want removed.

You said they are dimensions, so I'm assuming the X is Always surrounded by numbers.
This formula will work if there's only One set of dimensions within the Text string:

Book1
	B	C	D
1	Fork Oil Seal Kit &DustCap		Fork Oil Seal Kit 41X54X11 &DustCap
2	Red Bear Table		Red Bear Table 60x31x40
3	Red Bear Table no size		Red Bear Table no size
4	Extra Large example		Extra Large 100x200X300 example
Sheet217

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(D1,TRIM(LEFT(SUBSTITUTE(MID(D1,MIN(FIND({0,1,2,3,4}+{0;5},D1&1/17)),255)," ",REPT(" ",99)),99)),""))

Formula copied down.

 

[jolivanes]

Just for the heck of it, until something normal comes around.
All data in Column A, starting at A2.

Sub AAAAC()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If InStr(InStrRev(c.Value, " ", InStr(1, c.Value, "x")) + 1, c.Value, " ") = 0 Then
            c.Offset(, 1).Value = Left(c.Value, InStrRev(c.Value, " ", InStr(1, c.Value, "x")) - 1)
                Else
            c.Offset(, 1).Value = Left(c.Value, InStrRev(c.Value, " ", InStr(1, c.Value, "x")) - 1) & Mid(c.Value, _
            InStr(InStrRev(c.Value, " ", InStr(1, c.Value, "x")) + 1, c.Value, " "), 99)
        End If
    Next c
End Sub

Non of the data, outside of the measurements, should have an "x" in it!

 

[Vikas Kumar]

Hi,

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(D1,TRIM(LEFT(SUBSTITUTE(MID(D1,MIN(FIND({0,1,2,3,4}+{0;5},D1&1/17)),255)," ",REPT(" ",99)),99)),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi jtakw,

Nice one........Can you please explain how this term works?

'MIN(FIND({0,1,2,3,4}+{0;5},D1&1/17))'