CODEWITHSUNDEEP
excel
Manash Baruah
Manash Baruah

Reputation: 1

MS Excel- extract special character from a cell

I have an excel with one column (say column A) having different data in no specific format. Some of the cells in the column contains some special characters too. I need to find which cell contains "*", "@", "_" and "!". and publish those characters in column B.

For example

Column A contains-
A1- ABVC_KL.....
A2- !1223.....
A3- UIL%%$00.....
A4- JOHN@456.....
A5- 9_90*YY!P.....

Then, the formula in column B should give this result in column B:

B1- _.....
B2- !.....
B3- null.....
B4- @.....
B5- _*!.....

Can someone help me with ths?

Upvotes: 0

Views: 3925

Answers (2)

Vilas Thenge
Vilas Thenge

Reputation: 1

Try below formula.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(G2),"a",),"b",),"c",),"d",),"e",),"f",),"g",),"h",),"i",),"j",),"k",),"l",),"m",),"n",),"o",),"p",),"q",),"r",),"s",),"t",),"u",),"v",),"w",),"x",),"y",),"z",),"0",),"1",),"2",),"3",),"4",),"5",),"6",),"7",),"8",),"9",)

Upvotes: 0

Frank Herfert
Frank Herfert

Reputation: 490

For each character you want to find, create a new column (you can hide/group them afterwards), and create a formula like =IFERROR( IF(SEARCH("!",A2)>0,TRUE),""). If you have a lot of character to check for, reference the string you want to search for from row 1 (example) like this =IFERROR( IF(SEARCH(B$1,$A2)>0,TRUE),""). This way you can copy the formula and it will always reference column A text values and row 1 string values to find.

This will search for the string "!" in cell A2 and give its location. You dont need to know the exact location, just if there is a character like this, so IF >0 provides a TRUE value if there is a "!" anywhere. If there is none, an error is displayed and turned into an empty string "" with the IFERROR().

Upvotes: 1

Related Questions