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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,

A bunch of Substitutes:


Excel 2010
AB
1(D+0.0625)+(L+.125)+(D+0.625)1.0625x2.125x1.625
Sheet1
Cell Formulas
RangeFormula
B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"D","1"),"L","2"),"0.","."),")+(","x"),"+",""),"(",""),")","")
 
Upvote 0
@ElectroBins, I've read your other post regarding a request on this string, please see sample below for, converting, summing, and multiplying:


Excel 2010
ABCD
1Original StringConverted String for DisplaySummedMultiplied
2(D+0.0625)+(L+.125)+(D+0.625)1.0625x2.125x1.6254.81253.668945313
Sheet1
Cell Formulas
RangeFormula
B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"D","1"),"L","2"),"0.","."),")+(","x"),"+",""),"(",""),")","")
C2=SUM(LEFT(B2,FIND("x",B2)-1),MID(B2,FIND("x",B2)+1,FIND("^",SUBSTITUTE(B2,"x","^",2))-FIND("x",B2)-1),MID(B2,FIND("^",SUBSTITUTE(B2,"x","^",2))+1,255))
D2=PRODUCT(LEFT(B2,FIND("x",B2)-1),MID(B2,FIND("x",B2)+1,FIND("^",SUBSTITUTE(B2,"x","^",2))-FIND("x",B2)-1),MID(B2,FIND("^",SUBSTITUTE(B2,"x","^",2))+1,255))
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top