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
kesimpulandari 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 jumlahkarakter di kanan data asal dengan
7 karakter formula di G2 data awal di A2
=LEFT(A2;LEN(A2)-7) .
langkah 2.
mencarinilai 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" <[email protected]>
Kepada: [email protected]
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#CCB0056*##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.
ghozi 05112011 ambil data dari deretan data.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
