generalsamad
2015/04/06, 17:38
با سلام
چند فرمول کاربردی در زمینه جداکردن اعداد درون یک سلول که شامل متن و عدد و سمبل هست قرار میدم.
بطور مثال مقدار سلول A1 باربر است با این رشته : generalsamad30+yr25/*^2
میخوام اعداد درون این سلول رو جدا کنم
از یکی از این 3 فرمول زیر به صورت آرایه میتونم استفاده کنم .
=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)
=SUM(MID(0&A1;LARGE(ISNUMBER(--MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1))*ROW(INDIRECT("1:"&LEN(A1)));ROW(INDIRECT("1:"&LEN(A1))))+1;1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)
=SUM(MID(A1;LARGE(ISNUMBER(--MID(A1;ROW($1:$25);1))*ROW($1:$25);ROW($A$1:INDEX( $A:$A;COUNT(--MID(A1;ROW($1:$25);1)))));1)*10^(ROW(INDEX($1:$25; COUNT(--MID(A1;ROW(INDEX($1:$25;1;1):INDEX($1:$25;LEN(A1); 1));1));1):INDEX($1:$25;1;1))-1))
خروجی هر سه فرمول طبق مثال ذکر شده برابر است با 30252
و اگه بخوایم اولین عددی که تا قبل از یک حرف رو جدا کنیم از این فرمول به صورت آرایه استفاده میکنیم
=LOOKUP(99^99;--("0"&MID(A1;MIN(SEARCH({0,1,2,3,4,5,6,7,8,9};A1&"0123456789"));ROW($1:$10000))))
خروجی این فرمول طبق مثال ذکر شده برابر است با 30
اگه سلول ما با حرف شروع شد و بخوایم رشته های اولی از این سلول تا قبل از اولین عدد رو استخراج کنیم به شرط آنکه سلول ما با رشته آغاز شده باشه میشه از این فرمول استفاده کرد(اولین رشته تا قبل از عدد)
=TRIM(LEFT(A1;MIN(FIND({0,1,2,3,4,5,6,7,8,9};A1&"0123456789"))-1))
خروجی این فرمول طبق مثال ذکر شده برابر است با generalsamad
فایل ضمیمه گردید
چند فرمول کاربردی در زمینه جداکردن اعداد درون یک سلول که شامل متن و عدد و سمبل هست قرار میدم.
بطور مثال مقدار سلول A1 باربر است با این رشته : generalsamad30+yr25/*^2
میخوام اعداد درون این سلول رو جدا کنم
از یکی از این 3 فرمول زیر به صورت آرایه میتونم استفاده کنم .
=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)
=SUM(MID(0&A1;LARGE(ISNUMBER(--MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1))*ROW(INDIRECT("1:"&LEN(A1)));ROW(INDIRECT("1:"&LEN(A1))))+1;1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)
=SUM(MID(A1;LARGE(ISNUMBER(--MID(A1;ROW($1:$25);1))*ROW($1:$25);ROW($A$1:INDEX( $A:$A;COUNT(--MID(A1;ROW($1:$25);1)))));1)*10^(ROW(INDEX($1:$25; COUNT(--MID(A1;ROW(INDEX($1:$25;1;1):INDEX($1:$25;LEN(A1); 1));1));1):INDEX($1:$25;1;1))-1))
خروجی هر سه فرمول طبق مثال ذکر شده برابر است با 30252
و اگه بخوایم اولین عددی که تا قبل از یک حرف رو جدا کنیم از این فرمول به صورت آرایه استفاده میکنیم
=LOOKUP(99^99;--("0"&MID(A1;MIN(SEARCH({0,1,2,3,4,5,6,7,8,9};A1&"0123456789"));ROW($1:$10000))))
خروجی این فرمول طبق مثال ذکر شده برابر است با 30
اگه سلول ما با حرف شروع شد و بخوایم رشته های اولی از این سلول تا قبل از اولین عدد رو استخراج کنیم به شرط آنکه سلول ما با رشته آغاز شده باشه میشه از این فرمول استفاده کرد(اولین رشته تا قبل از عدد)
=TRIM(LEFT(A1;MIN(FIND({0,1,2,3,4,5,6,7,8,9};A1&"0123456789"))-1))
خروجی این فرمول طبق مثال ذکر شده برابر است با generalsamad
فایل ضمیمه گردید