Substitute character

[12Rev79]

Dear Experts,

Please is there a way to substitute character like below
old format New Format
A71-DBC-3529_E05101A/F-1 A71-DBC-3529/E05101A_F/1
U71-WOS-0801_B08201L/E-23 U71-WOS-0801/B08201L_E/23
A98-FAC-70221_H06504G/H-84 A98-FAC-70221/H06504G_H/84

Thank you very much for any help appreciated.

12Rev79

 

[Alex Blakenburg]

The sample data format looks pretty consistent. Here is one way of doing it.
Note: If you only need it to work on MS365 then I am sure others can give you more options.

20250221 Substitute Characters 12Rev79.xlsx
	A	B	C	D
1	Original	Formula	Expected	Match
2	A71-DBC-3529_E05101A/F-1	A71-DBC-3529/E05101A_F/1	A71-DBC-3529/E05101A_F/1	TRUE
3	U71-WOS-0801_B08201L/E-23	U71-WOS-0801/B08201L_E/23	U71-WOS-0801/B08201L_E/23	TRUE
4	A98-FAC-70221_H06504G/H-84	A98-FAC-70221/H06504G_H/84	A98-FAC-70221/H06504G_H/84	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_","/"),"/","_",2),"-","/",3)
D2:D4	D2	=B2=C2

 

[Rick Rothstein]

@Alex Blakenburg's solution is the simplest to implement and understand... and it will work in any version of Excel. However, if you are working in XL365 only, then this formula would also work...

=TEXTJOIN({"-","-","/","_","/"},,TEXTSPLIT(A2,{"-","_","/"}))

 

[12Rev79]

The sample data format looks pretty consistent. Here is one way of doing it.
Note: If you only need it to work on MS365 then I am sure others can give you more options.

20250221 Substitute Characters 12Rev79.xlsx
	A	B	C	D
1	Original	Formula	Expected	Match
2	A71-DBC-3529_E05101A/F-1	A71-DBC-3529/E05101A_F/1	A71-DBC-3529/E05101A_F/1	TRUE
3	U71-WOS-0801_B08201L/E-23	U71-WOS-0801/B08201L_E/23	U71-WOS-0801/B08201L_E/23	TRUE
4	A98-FAC-70221_H06504G/H-84	A98-FAC-70221/H06504G_H/84	A98-FAC-70221/H06504G_H/84	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_","/"),"/","_",2),"-","/",3)
D2:D4	D2	=B2=C2

Thank you so much @Alex Blakenburg
Sir, it works perfectly appreciate your help...

Thanks again

12Rev79

 

[12Rev79]

@Alex Blakenburg's solution is the simplest to implement and understand... and it will work in any version of Excel. However, if you are working in XL365 only, then this formula would also work...

=TEXTJOIN({"-","-","/","_","/"},,TEXTSPLIT(A2,{"-","_","/"}))

​

Thanks too @Rick Rothstein Sir, I learned new stuff...you are all great...​

I am also using Excel 365
cheers for all your kindness
12Rev79

 

[Alex Blakenburg]

You're welcome. Glad we could help.