Hot Fox: Bls: ]] XL-mania [[ Ambil data dari deretan data

sábado, 5 de novembro de 2011

Bls: ]] XL-mania [[ Ambil data dari deretan data

formula globalnya adalah (formula array)

LEN(A2)-7))-LEN(SUBSTITUTE(LEFT(A2;LEN(A2)-7);"$";""))-1)+1;7);FIND("$";MID(LEFT(A2;LEN(A2)-7);SMALL(IF(MID(LEFT(A2;LEN(A2)-7);ROW(1:$200);1)="$";ROW(1:$200));LEN(LEFT(A2;LEN(A2)-7))-LEN(SUBSTITUTE(LEFT(A2;LEN(A2)-7);"$";""))-1)+1;7));10;"")

pennjelasan dari formula global

kesimpulan dari kecenderungan data

1. posisi string data yang ingin diambil selalu diapit tanda dolar ($).

2. karakter di belakang string yang ingin diambil ada yang masih mengandung tanda dolar ada yang tidak.

untuk solusinya dibutuhkan 4 langkah

langkah1.

untuk memastikan tidak ada karakter ($) lagi setelah ($) yang mengapit string yang diinginkan adalah mengurangi jumlah karakter di kanan data asal dengan 7 karakter formula di G2 data awal di A2

=LEFT(A2;LEN(A2)-7) .

langkah 2.

mencari nilai posisi karakter ($) pertama yang mengapit karakter dari hasil langkah 1 di G2 formula di F2

{=SMALL(IF(MID(G2;ROW(1:$200);1)="$";ROW(1:$200));LEN(G2)-LEN(SUBSTITUTE(G2;"$";""))-1)+1}

dan menghasilkan nilai karakter ke berapa dimulainya string yang diiginkan

langkah3.

hasil dari langkah 2 dijadikan acuan untuk mengambil string yang diinginkan formula di I2

=MID(G2;H2;10)

yang menghsilkan string yang masih mengandung ($) penutup string yang diinginkan.

langkah 4.

membuang karakter ($) dan seluruh karakter di sebelah kanannya dengan fungsi formula di J2

=REPLACE(I2;FIND("$";I2);10;"")

lebih jelasnya bisa dilihat di attachment.

wassalam

Ghozi Alkatiri

Dari: "Anggana, Agung" <agung.anggana@sc.com>
Kepada: xl-mania@yahoogroups.com
Dikirim: Kamis, 3 November 2011 9:19
Judul: ]] XL-mania [[ Ambil data dari deretan data

Temans,

Bisa bantu untuk cara formula yang ini

DATA AWAL	Data hasil Yang Di inginkan
EC453#JAKARTA#TARAMARA#13X#$13254rt12345$#I56DY#ESN#bblk:##FORMNUM:#$E19$DI:60000000$$	E19
TD3225#TRUNOTO#SAMIRI#14x#$111021CDA0054$#?J02SN?#ESN#$DI:, 15,500,000$#$#LINT#$A66$#NTCRS##~XTV251011~#TGB#	A66
MTB2109#RANTASTE#EMP MBNTR KLRS#13X#$110923E#ESN#CLR SCR PASS#FORMNUM:#110923CLS0006#$E3008$DI:5000000$NINI$	E3008
MB1358#TGRNGT#TYRNTR#$110920A060013$#?J01JP?#ESN#&#CLEAR#&#19CCB0056*##SCR:PASS#$DI:8,500,000$#$#DNMTY#$X22$##TYGHF#DI$	X22
KP8333#$#TANJUNG PINANG #MISNAR#$00A111101CDB0112$#?J03AD?#ESN#$DI:, 10,000,000$#$#NAWON KYUNG#$A26$#TKLRJR#	A26

Terimakasih

This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

New Members 51

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| ayooo... coba cari XL-mania di linkedin.com                       |
| selamat merayakan idul adha!                                      |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.