changing display of a string

[ElectroBins]

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.

 

[jtakw]

Hi,

A bunch of Substitutes:

Excel 2010
	A	B
1	(D+0.0625)+(L+.125)+(D+0.625)	1.0625x2.125x1.625
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"D","1"),"L","2"),"0.","."),")+(","x"),"+",""),"(",""),")","")

 

[FDibbins]

Where is this data coming from?

 

[mole999]

A bit more of the puzzle is here in this locked thread > http://www.mrexcel.com/forum/excel-questions/939737-converting-text-formula.html#post4514298

 

[jtakw]

@ElectroBins, I've read your other post regarding a request on this string, please see sample below for, converting, summing, and multiplying:

Excel 2010
	A	B	C	D
1	Original String	Converted String for Display	Summed	Multiplied
2	(D+0.0625)+(L+.125)+(D+0.625)	1.0625x2.125x1.625	4.8125	3.668945313
Sheet1

Cell Formulas
Range	Formula
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))

 

[ElectroBins]

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.