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

[jonathan92591]

[TABLE="width: 1341"]
<tbody>[TR]
[TD]Excel version:[/TD]
[TD]Excel 2016[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Computer operating system:[/TD]
[TD]Windows 7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample data:[/TD]
[TD]Sheet1

*	A
Text Needed to Change
24" Left Hinge Built-In ADA Compliant Beverage Center
24" Right Hinge Built-In ADA Compliant Beverage Center
24" Left Hinge Built-In ADA Compliant Beverage Center

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formulas/functions being used:[/TD]
[TD] = SUBSTITUTE(SUBSTITUTE(A2,"Left","Right"),"Right","Left")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current results:[/TD]
[TD]Sheet1

*	A	B
Text Needed to Change	Attempt at Changing Text
XXX	24" Left Hinge Built-In ADA Compliant Beverage Center
XXX	24" Left Hinge Built-In ADA Compliant Beverage Center
24" Left Hinge Built-In ADA Compliant Beverage Center	24" Left Hinge Built-In ADA Compliant Beverage Center

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</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 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]My goal:[/TD]
[TD]Change "Left" to "Right." And, Change "Right" to "Left."[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Error message:[/TD]
[TD]No error message.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]How error occurred:[/TD]
[TD]Not working correctly.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Generated in:[/TD]
[TD]Excel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thank you, JT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.