I have the following Data Validation options in cells in Column A: 0, <50, 50+, 100+, 250+, 500+, 1000+, 2500+, 5000+, Call
I want to automatically have them appear as numeric values in Column B as these corresponding values: 0, 50, 50, 100, 250, 500, 1000, 2500, 5000, 100
ie data at A1
(formula array)
=MAX(IFERROR(--MID(A1,ROW(1:10),SUMPRODUCT((ISNUMBER(--MID(A1,ROW(1:10),1)))*1)),""))
Data start A1 :
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1,$25)/10)
Azumi
Is the data validation directly entered into the DV refers to? or is the list a range of cells stored elsewhere ?
if directly entered you could try this :
(assuming DV in A1) B1:
Formula:
Please Login or Register to view this content.
if stored elsewhere, say Z1-Z10, then put the values in AA1-AA10 and use either
Formula:Please Login or Register to view this content.
or
Formula:Please Login or Register to view this content.
Hope these help
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
=IFERROR(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"<",""),"+",""),"call",100),"")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks, this one works great, except for the value Call is not being replaced with 100 or any other valueOriginally Posted by martindwilson
change call to Call substitute is case sensitive
or
use
=IFERROR(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"<",""),"+",""),"CALL",100),"")
Ah, i didn't notice, that does it. Thanks Again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks