Convert Text to Formula

[sumeetanand1980]

Greetings,

Is it possible to convert the TEXT string into a formula/number?

Example Source in text format (essentially substring of another text):
(16+1)
or
(17*1)

Expected Result in numeric data format:
17

 

[Saba Sabaratnam]

Hi

Normally you can carry out calculations on numbers stored as text.

However, you can covert number text string into number by multiplying it by 1 as shown below.


=(A1*1)+(B1*1)

Kind regards

Saba

 

[sumeetanand1980]

Thanks for the prompt reply, the example was just a snippet of a complex string.

The source string is much complex, and can be VERY LARGE:

TEXT format:
(16+5+4-2.5-1.5)

Assuming above is at cell A1, then:
=A1*1 or A1+0
Result: #VALUE

=VALUE(A1)
Result:#VALUE

=INDIRECT(A1) or INDIRECT(A1)*1
Result:#REF!

="="&A1
Result: =(16+5+4-2.5-1.5)

 

[Saba Sabaratnam]

I think that I misunderstood your question.

Assuming you have the following structure irrespective length of your data

(16+5+4-2.5-1.5)

Insert a space in front of your data using the following formula in B3 and copy it down

=" "&A3

Then copy from B3 to B5 and paste a value

Select the column B and Find space ( put curser in Find What and press space bar once) and Replace it with =

The formula in B3 and it should work. Please see below.
=(16+5+4-2.5-1.5)


=

Kind regards

Saba

 

[lezawang]

Hi, I tried this for you but I got rid of the ( ) but the formula did not work.

Book1
	A	B
1	(16+1)	=16+1
Sheet1

Cell Formulas
Range	Formula
B1	B1	="="&SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")

 

[JoeMo]

If the text always has the format you show, here's a UDF that will return the summation of the numbers within the parentheses:

Book1
	A	B
1	(16+5+4-2.5-1.5)	21
2	(-10.5-15.5+7+21-3)	-1
3	(-21+17-50.0+25.5-3-6+12-23)	-48.5
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=ConvertIt(A1)

Function ConvertIt(S As String) As Variant
Dim V As Variant
S = Replace(Replace(Replace(S, "-", "+-"), "(", ""), ")", "")
V = Split(S, "+")
For i = LBound(V) To UBound(V)
    If V(i) <> "" Then ConvertIt = ConvertIt + CDbl(V(i))
Next i
End Function

 

[sumeetanand1980]

If the text always has the format you show, here's a UDF that will return the summation of the numbers within the parentheses:

Book1
	A	B
1	(16+5+4-2.5-1.5)	21
2	(-10.5-15.5+7+21-3)	-1
3	(-21+17-50.0+25.5-3-6+12-23)	-48.5
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=ConvertIt(A1)

Function ConvertIt(S As String) As Variant
Dim V As Variant
S = Replace(Replace(Replace(S, "-", "+-"), "(", ""), ")", "")
V = Split(S, "+")
For i = LBound(V) To UBound(V)
    If V(i) <> "" Then ConvertIt = ConvertIt + CDbl(V(i))
Next i
End Function

I think that I misunderstood your question.

Assuming you have the following structure irrespective length of your data

(16+5+4-2.5-1.5)

Insert a space in front of your data using the following formula in B3 and copy it down

=" "&A3

View attachment 40579

Then copy from B3 to B5 and paste a value

Select the column B and Find space ( put curser in Find What and press space bar once) and Replace it with =

View attachment 40581

The formula in B3 and it should work. Please see below.
=(16+5+4-2.5-1.5)


=View attachment 40580

Kind regards

Saba

The end results is what am trying to get, however, via a formula. The spreadsheet consists of multiple sheets/tables, with thousands of rows , and the source is one the columns. Need to get numeric value of the column to be used in another place.

 

[sumeetanand1980]

If the text always has the format you show, here's a UDF that will return the summation of the numbers within the parentheses:

Book1
	A	B
1	(16+5+4-2.5-1.5)	21
2	(-10.5-15.5+7+21-3)	-1
3	(-21+17-50.0+25.5-3-6+12-23)	-48.5
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=ConvertIt(A1)

Function ConvertIt(S As String) As Variant
Dim V As Variant
S = Replace(Replace(Replace(S, "-", "+-"), "(", ""), ")", "")
V = Split(S, "+")
For i = LBound(V) To UBound(V)
    If V(i) <> "" Then ConvertIt = ConvertIt + CDbl(V(i))
Next i
End Function

the end result is what am aiming for, however, is it possible to do without a script - with just formulas?

 

[sumeetanand1980]

I tried something different and think am close.

Converted the text into XML via Formula:


=FILTERXML("<Total><s>"

& SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A2,"+","</s><s>" ),
"-","</s><s>-"),
"(",""),
")","")

& "</s></Total>","//s")


--
So TEXT like : (16+5+4-2.5-1.5) can be converted elsewhere fine.

Now, I need to XPATH in the above expression that can calculate sum in one go.

I tried "sum(//s)" but that results in #VALUE.

Any suggestions on this?

 

[sumeetanand1980]

SOLVED!!!

Got it. I just used SUM(FILTERXML...) and got the expected results. Here is the solution (PHEW):



=SUM(FILTERXML("<Total><s>"

& SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A2,"+","</s><s>" ),
"-","</s><s>-"),
"(",""),
")","")

& "</s></Total>","//s"))