Trying to apply two substitutes to one cell, Left becomes Right, and Right becomes Left

[jonathan92591]

Excel version:	Excel 2016
Computer operating system:	Windows 7
Sample data:	Sheet1 * A 1 Text Needed to Change 2 24" Left Hinge Built-In ADA Compliant Beverage Center 3 24" Right Hinge Built-In ADA Compliant Beverage Center 4 24" Left Hinge Built-In ADA Compliant Beverage Center <tbody> </tbody> Excel tables to the web >> Excel Jeanie HTML 4
Formulas/functions being used:	= SUBSTITUTE(SUBSTITUTE(A2,"Left","Right"),"Right","Left")
Current results:	Sheet1 * A B 1 Text Needed to Change Attempt at Changing Text 2 XXX 24" Left Hinge Built-In ADA Compliant Beverage Center 3 XXX 24" Left Hinge Built-In ADA Compliant Beverage Center 4 24" Left Hinge Built-In ADA Compliant Beverage Center 24" Left Hinge Built-In ADA Compliant Beverage Center <tbody> </tbody> Spreadsheet Formulas Cell Formula B2 =SUBSTITUTE(SUBSTITUTE(A2,"Left","Right"),"Right","Left") B3 =SUBSTITUTE(SUBSTITUTE(A3,"Left","Right"),"Right","Left") B4 =SUBSTITUTE(SUBSTITUTE(A4,"Left","Right"),"Right","Left") <tbody> </tbody> <tbody> </tbody> Excel tables to the web >> Excel Jeanie HTML 4
My goal:	Change "Left" to "Right." And, Change "Right" to "Left."
Error message:	No error message.
How error occurred:	Not working correctly.
Generated in:	Excel
Thank you, JT

<tbody>
</tbody>

 

[MarcelBeug]

You can try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Left","!@#"),"Right","Left"),"!@#","Right")

 

[jonathan92591]

You can try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Left","!@#"),"Right","Left"),"!@#","Right")

Success! Thank you very much kind sir.

 

[jtakw]

Hi,

Another way:

Excel 2010
	A	B	C
1	Text Needed to Change
2	24" Left Hinge Built-In ADA Compliant Beverage Center		24" Right Hinge Built-In ADA Compliant Beverage Center
3	24" Right Hinge Built-In ADA Compliant Beverage Center		24" Left Hinge Built-In ADA Compliant Beverage Center
4	24" Left Hinge Built-In ADA Compliant Beverage Center		24" Right Hinge Built-In ADA Compliant Beverage Center
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ISNUMBER(SEARCH("Left",A2)),SUBSTITUTE(A2,"Left","Right",1),SUBSTITUTE(A2,"Right","Left",1))

 

[wesborland1234]

As long as no cells contain both "Left" and "Right", this will work:

=IF(NOT(ISERR(FIND("Left",A1,1))),SUBSTITUTE(A1,"Left","Right"),SUBSTITUTE(A1,"Right","Left"))

With the nested SUBSTITUTE, you were changing Left to Right, and then right back to Left again.

This statement first checks if the cell has "Left", and if it does, changes "Left" to "Right", and if not, changes "Right" to "Left".

 

[jonathan92591]

Thank you.

 

[jonathan92591]

As long as no cells contain both "Left" and "Right", this will work:

=IF(NOT(ISERR(FIND("Left",A1,1))),SUBSTITUTE(A1,"Left","Right"),SUBSTITUTE(A1,"Right","Left"))

With the nested SUBSTITUTE, you were changing Left to Right, and then right back to Left again.

This statement first checks if the cell has "Left", and if it does, changes "Left" to "Right", and if not, changes "Right" to "Left".

Excellent point.