'pattern removing in excel (include symbol and words) [duplicate]
I have an excel column with pattern like this:
_17 "17" [factor=17],
_26 "26" [factor=26],
_30 "30" [factor=30],
_32 "32" [factor=32],
I have read some posts regarding using substitute in excel, but am not sure how should I do it.
The output I want is to keep only the number as shown:
17
26
30
32
Any help would be greatly appericated.
Solution 1:[1]
You can use something like MID.
mid(col,2,2)
Solution 2:[2]
You can use FILTERXML() which is more versatile.
=FILTERXML(SUBSTITUTE(SUBSTITUTE(A1,"_","<t><s>")," ","</s><s>")&"</s></t>","//s[1]")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Pankaj |
| Solution 2 | Harun24hr |