Hi, I've a problem to set formulas, say in column B using substitute function to replace column A text
Column A
A1: +2M+4M
A2: +1M+3.5M
A3: +0.5M+3.6M
Column B
B1: +Value(Substitute(A1,"M",""))
B2: +Value(Substitute(A2,"M",""))
B3: +Value(Substitute(A3,"M",""))
The result in column B shows error #Value!
Grateful if anyone could help.
Many thanks.
First, use "=" instead of "+" to begin a formula.
Second, when you execute SUBSTITUTE you get these results:
+2+4
+1+3.5
+0.5+3.6
None of those are numbers, they are arithmetic expressions. VALUE won't evaluate an expression for you. It just turns the string "123" into the number 123.
You need to use VBA for what you want.
B1: =Eval(SUBSTITUTE(A1,"M",""))
Please Login or Register to view this content.
and so forth.
First define a name, the name is CAL, and the placement formula is =EVALUATE(SUBSTITUTE($A1,"M",))
Cell B1 formula =CAL , Drag down
Interesting that EVALUATE can be used in a defined name but not in a worksheet cell.
For 6stringjazzer : I cannot make the codes work, with same error #value! *I've uploaded the Excel for your checking
For wk9128 : I don't know where to define a name
Hope you could further advise.
Thx
Please try
=SUMPRODUCT(--MID(SUBSTITUTE(SUBSTITUTE(A1,"M",),"+",REPT(" ",20)),{20,40},20))
or
=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A1,"M",),"+",REPT(" ",20)),ROW(A$1:A$9)*20,20),"0.0;-0.0;0;\0"))
The attachments and formulas have been uploaded, and they are all in the attachments
Dear ALL,
Thank you so much for the response.
I've tested all options, and find using the codes is the best as column A may involve more than 2 items and sometimes minus signs as well.
Thanks again
Edward
@chasoe You're Welcome. Glad to help . Thank You for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks