You forgot to say that the other text in the cell could also contain "X"
I did not say that - please read my reply againI do not know why you believe in the formula over my feedback
Please reconsider your rule"The only consistent rule is that the word I want to be rid off in all my cells contains the letter x"
"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.
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)),"")) |
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
Hi,
Worksheet Formulas
Cell 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)),""))
<tbody>
</tbody>
<tbody>
</tbody>