Removing Unnecessary Characters from Excel Cell

Question

Below is a listing of some cells with unnecessary text. The text to remove would be /%%, -, and empty spaces.

Text and Result | Text | Result | |:--------|:---------| | DW80R201UB/AA| DW80R201UB | | DW80R201UW/AA| RDW80R201UW | | DWT24PNA12| RDWT24PNA12 | | DV-2A/XAA| RDV2A | | 1DV-MCK/A1| RDVMCK | | 1HAFCU1/XAA| RHAFCU1 | | HAF-CIN/EXP| RHAFCIN |

For entries with the forward slash, I use =SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND("/",A1)+1),"") since there can be more than one character after the forward slash.

For everything else, I would use =SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","").

I'll usually use the first formula, and then filter the column to only get #VALUE results and use the second formula. I'm just wondering if there is an easier way to get all the models with one nested function.

Answer 1

Take all characters to the left of a forward slash. If there's no forward slash, then take the original value. From there, substitute any dash or space with an empty string.

=SUBSTITUTE(SUBSTITUTE(IFERROR(LEFT(A1,FIND("/",A1,1)-1),A1),"-","")," ","")