changing display of a string

ElectroBins

New Member
Joined
May 6, 2016
Messages
6
Hi.
I have the following in a cell that is a text. (D+0.0625)+(L+.125)+(D+0.625). I am able to substitute D with the the value 1 and L with the value 2 to give me (1+0.0625)+(2+.125)+(1+0.625). I would like to display it as1.0625x2.125x1.0625

the formula must add the values between the brackets. The cross is not multiplication it is a "x" and is needed for display purposes.
each bracket gives me a specific size so I cannot combine them.


I would like to be display my string of characters described above with a "x" in between.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

A bunch of Substitutes:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">(D+0.0625)+(L+.125)+(D+0.625)</td><td style=";">1.0625x2.125x1.625</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A1,"D","1"</font>),"L","2"</font>),"0.","."</font>),")+(","x"</font>),"+",""</font>),"(",""</font>),")",""</font>)</td></tr></tbody></table></td></tr></table><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
@ElectroBins, I've read your other post regarding a request on this string, please see sample below for, converting, summing, and multiplying:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Original String</td><td style=";">Converted String for Display</td><td style=";">Summed</td><td style=";">Multiplied</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">(D+0.0625)+(L+.125)+(D+0.625)</td><td style=";">1.0625x2.125x1.625</td><td style="text-align: right;;">4.8125</td><td style="text-align: right;;">3.668945313</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A2,"D","1"</font>),"L","2"</font>),"0.","."</font>),")+(","x"</font>),"+",""</font>),"(",""</font>),")",""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=SUM(<font color="Blue">LEFT(<font color="Red">B2,FIND(<font color="Green">"x",B2</font>)-1</font>),MID(<font color="Red">B2,FIND(<font color="Green">"x",B2</font>)+1,FIND(<font color="Green">"^",SUBSTITUTE(<font color="Purple">B2,"x","^",2</font>)</font>)-FIND(<font color="Green">"x",B2</font>)-1</font>),MID(<font color="Red">B2,FIND(<font color="Green">"^",SUBSTITUTE(<font color="Purple">B2,"x","^",2</font>)</font>)+1,255</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=PRODUCT(<font color="Blue">LEFT(<font color="Red">B2,FIND(<font color="Green">"x",B2</font>)-1</font>),MID(<font color="Red">B2,FIND(<font color="Green">"x",B2</font>)+1,FIND(<font color="Green">"^",SUBSTITUTE(<font color="Purple">B2,"x","^",2</font>)</font>)-FIND(<font color="Green">"x",B2</font>)-1</font>),MID(<font color="Red">B2,FIND(<font color="Green">"^",SUBSTITUTE(<font color="Purple">B2,"x","^",2</font>)</font>)+1,255</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

ElectroBins

New Member
Joined
May 6, 2016
Messages
6
Let me go in details a little.
these formulas are for different styles of corrugated boxes created long before computers came about. Now, We typically have a user interface for sales, or customer service or even clients to input The closed internal box dimensions or the length x widths x depth are the inside size of the closed box. Our formulas takes the internal dimensions and works behind the scenes to give us flat sheet width and length of the corrugated sheet we need to purchase. More so, Each bracket represents the scores position needed on the sheet. I.e. The outcome is that we send an order for flat sheet size and position of scores. This is an industrial standard so there is no possibility of changing the format.

There are hundreds of formulas (not in Excel) and I would like to take these formulas, substitute the "D"s, the "L"s, etc, etc with the values in cells to give me dimensions. At the same time I would like to calculate the values between each bracket, put a "x" between them to give me a dimensional representation of my order.

i tried replacing the Ds and Ls with cell locations and then adding a = sign and this did not work ("="&A1) where A1 is the formula.

The only thing that worked is creating an "evaluate" formula in name manager ( after substituting). I don't know why EVALUTE was removed in Excel 2007 up.

Thank you jtakw. I will try your suggestion in Two days (Monday) and let you know.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,556
Messages
5,637,036
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top