[belajar-excel] Fwd: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

classic Classic list List threaded Threaded
5 messages Options
Kid
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Fwd: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

Kid
Administrator
---------- Forwarded message ---------
From: Yahoo Groups Notification <
[hidden email]>
Date: Wed, Jan 8, 2020 at 9:32 AM
Subject: MODERATE -- [hidden email] posted to belajar-excel
To: <[hidden email]>



Hello,

A message has been sent to the belajar-excel group from

  [hidden email]

A complete copy of this message has been attached for your convenience.


---------- Forwarded message ----------
From: halak hita <[hidden email]>
To: [hidden email]
Cc:
Bcc:
Date: Wed, 8 Jan 2020 09:31:35 +0700
Subject: Formula pemisahan code
Dear master excel
Tolong dibantu apa formula ambil code id seperti terlampir.



Terima kasih

Halkit

Formula pemisahan.xlsx (12K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

Kid
Administrator
PN= Left( teks , 15 )
Brand= Mid( teks , 17 , 99 )



On Thu, Jan 9, 2020 at 12:01 AM Mr. Kid <[hidden email]> wrote:

>
>
> ---------- Forwarded message ---------
> From: Yahoo Groups Notification <
> [hidden email]>
> Date: Wed, Jan 8, 2020 at 9:32 AM
> Subject: MODERATE -- [hidden email] posted to belajar-excel
> To: <[hidden email]>
>
>
>
> Hello,
>
> A message has been sent to the belajar-excel group from
>
>   [hidden email]
>
> A complete copy of this message has been attached for your convenience.
>
>
> ---------- Forwarded message ----------
> From: halak hita <[hidden email]>
> To: [hidden email]
> Cc:
> Bcc:
> Date: Wed, 8 Jan 2020 09:31:35 +0700
> Subject: Formula pemisahan code
> Dear master excel
> Tolong dibantu apa formula ambil code id seperti terlampir.
>
>
>
> Terima kasih
>
> Halkit
>
>
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

halak hita
Terima kasih mr.kid
Namun bagaimana FORMULA nya jika text di PN tidak standar atau tidak selalu
15 sebelum tanda "-".
Sebab PN ini bervariasi jumlah karakternya..

Misalnya

SC12345-R1-MAUL
RT1234-R1-MOD
V0123456789-WEW
V123456-MAUL
ZA636721-1-MODE
ZA7200891600-R2-DEL
R123456778901-R10-SAZH
A0003195008-REN
ZA6443800702-ZR
78008002400-ERT
8000A2200-01-DT

Bagaimana Formulanya agar PN bisa diambil sebelum tanda "-" terakhir

Hasil yg diinginkan
SC12345-R1
78008002400
800A2200-01




Pada tanggal Kam, 9 Jan 2020 18:33, 'Mr. Kid' [hidden email]
[belajar-excel] <[hidden email]> menulis:

>
>
> PN= Left( teks , 15 )
> Brand= Mid( teks , 17 , 99 )
>
>
>
> On Thu, Jan 9, 2020 at 12:01 AM Mr. Kid <[hidden email]> wrote:
>
>>
>>
>> ---------- Forwarded message ---------
>> From: Yahoo Groups Notification <
>> [hidden email]>
>> Date: Wed, Jan 8, 2020 at 9:32 AM
>> Subject: MODERATE -- [hidden email] posted to belajar-excel
>> To: <[hidden email]>
>>
>>
>>
>> Hello,
>>
>> A message has been sent to the belajar-excel group from
>>
>>   [hidden email]
>>
>> A complete copy of this message has been attached for your convenience.
>>
>>
>> ---------- Forwarded message ----------
>> From: halak hita <[hidden email]>
>> To: [hidden email] <belajar-excel@yahoogroups..com>
>> Cc:
>> Bcc:
>> Date: Wed, 8 Jan 2020 09:31:35 +0700
>> Subject: Formula pemisahan code
>> Dear master excel
>> Tolong dibantu apa formula ambil code id seperti terlampir.
>>
>>
>>
>> Terima kasih
>>
>> Halkit
>>
>>
>
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

Kid
Administrator
Konsep :
  ubah delimiter terakhir menjadi '|', lalu cari posisi karakter '|', dan
ambil semua karakter sebelum posisi karakter '|' tersebut

Syntax Formula :
   PN =IFError( Left( teks , Find( pengganti_delimiter , Substitute( teks ,
delimiter , pengganti_delimiter , Len( teks ) - Len( Substitute( teks ,
delimiter , "" ) ) ) ) -1 ) , teks )
   Brand =Mid( teks , Len( hasil_PN ) + 1 + jumlah_karakter_delimiter ,
jumlah_karakter_terbanyak_field_terakhir )

Bagian-bagian formula :
  teks : data bertipe string
  delimiter : characters pemisah antar field
  pengganti_delimiter : characters pengganti delimiter terakhir
  hasil_PN : cells hasil formula PN
  jumlah_karakter_delimiter : jumlah karakter delimiter yang digunakan,
pada contoh formula PN di atas, teks menggunakan delimiter '-', maka nilai
ini adalah 1 karakter
  jumlah_karakter_terbanyak_field_terakhir : jumlah karakter terbanyak yang
mungkin dicapai oleh field terakhir

Catatan :
1. IFError digunakan untuk kondisi teks tidak berisi delimiter yang bisa
diubah menjadi '|', yang berarti harus mengambil teks apa adanya.
2. Len()-Len(Substitute) : banyaknya delimiter di dalam teks
    *** Jadi, Len()-Len(Substitute)+1 adalah banyaknya fields (atau kolom)
yang terbentuk jika dipisah dengan delimiter tersebut.
3. Formula ini lebih aman dan karakter '|' bisa disesuaikan dengan kondisi
setempat, walaupun cukup panjang.

Contoh :
1. data di A1 bertipe text (string), dengan delimiter karakter '-' (1
karakter delimiter), field terakhir maksimal berisi 99 karakter
    data tidak pernah berisi karakter '|', sehingga karakter '|' bisa
dijadikan pengganti_delimiter
         PN di B1 =IFError( Left( A1 , Find( "|" , Substitute( A1 , "-" ,
"|" , Len( A1 ) - Len( Substitute( A1 , "-" , "" ) ) ) ) -1 ) , A1 )
         Brand di C1 =Mid( A1 , Len( B1 ) + 1 + 1 , 99 )   atau   =Mid( A1
, Len( B1 ) + 2 , 99 )

2. data di A1 bertipe text (string), dengan delimiter karakter '-' (1
karakter delimiter), field terakhir maksimal berisi 999 karakter
    data berisi berbagai macam karakter termasuk koma, pipe, enter, tab,
dan sebagainya,
    sehingga pengganti_delimiter disusun sendiri dengan suatu tulisan yang
pasti tidak pernah digunakan oleh data, misal berbunyi '<kid ganteng>'
    *** penampakan salah satu data : ABC-XYZ|123-TES
         PN di B1 =IFError( Left( A1 , Find( "<kid ganteng>" , Substitute(
A1 , "-" , "<kid ganteng>" , Len( A1 ) - Len( Substitute( A1 , "-" , "" ) )
) ) -1 ) , A1 )
         Brand di C1 =Mid( A1 , Len( B1 ) + 1 + 1 , 99 )   atau   =Mid( A1
, Len( B1 ) + 2 , 999 )


>> Nice to know <<
Formula yang lebih pendek : (formula brand dapat menggunakan formula yang
telah dijelaskan di atas)
     *** Array Formula, pastikan di-entry dengan CTRL SHIFT ENTER ***
          PN =IFError( Left( teks ,   Match( 2 ,                  1/(Mid(
teks , Row($1:$999) , 1 ) =delimiter)                                 ) -1
) , teks )

     Catatan :
     1. Array formula untuk teks berisi maksimal 999 karakter -> lihat
angka 999 di bagian Row()
     2. Semakin banyak jumlah karakter, maka semakin lambat proses
kalkulasinya
         *** Jika jumlah karakter maksimal pada setiap record dari seluruh
data bisa diketahui, maka gunakanlah angka tersebut.
         *** misal diketahui jumlah karakter dalam setiap record maksimal
adalah 71 karakter, maka ubah  Row($1:$999) menjadi  Row($1:$71)
     3. Hati-hati dengan ulah user dalam men-Delete atau meng-Insert Entire
Row di area yang dirujuk oleh bagian Row(), karena akan mengubah formula

     Konsep :
        mencari posisi terakhir delimiter dalam teks, lalu mengambil semua
karakter sebelum posisi terakhir delimiter tersebut

     Pengembangan Formula :
        Array formula di atas bisa diubah agar tidak lagi menjadi array
formula, yaitu dengan salah satu dari cara berikut :
        A. memanfaatkan fungsi Index
             PN =IFError( Left( teks ,   Match( 2 ,   Index(   1/(Mid( teks
, Row($1:$999) , 1 ) =delimiter)     , 0  )                       ) -1 ) ,
teks )
        B. memanfaatkan fungsi LookUp
             PN =IFError( Left( teks , Lookup( 2 ,                1/(Mid(
teks , Row($1:$999) , 1 ) =delimiter)     , Row($1:$999)     ) -1 ) , teks )


Regards,
Kid



On Sat, Jan 11, 2020 at 8:05 PM halak hita [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Terima kasih mr.kid
> Namun bagaimana FORMULA nya jika text di PN tidak standar atau tidak
> selalu 15 sebelum tanda "-".
> Sebab PN ini bervariasi jumlah karakternya..
>
> Misalnya
>
> SC12345-R1-MAUL
> RT1234-R1-MOD
> V0123456789-WEW
> V123456-MAUL
> ZA636721-1-MODE
> ZA7200891600-R2-DEL
> R123456778901-R10-SAZH
> A0003195008-REN
> ZA6443800702-ZR
> 78008002400-ERT
> 8000A2200-01-DT
>
> Bagaimana Formulanya agar PN bisa diambil sebelum tanda "-" terakhir
>
> Hasil yg diinginkan
> SC12345-R1
> 78008002400
> 800A2200-01
>
>
>
>
> Pada tanggal Kam, 9 Jan 2020 18:33, 'Mr. Kid' [hidden email]
> [belajar-excel] <[hidden email]> menulis:
>
>>
>>
>> PN= Left( teks , 15 )
>> Brand= Mid( teks , 17 , 99 )
>>
>>
>>
>> On Thu, Jan 9, 2020 at 12:01 AM Mr. Kid <[hidden email]> wrote:
>>
>>>
>>>
>>> ---------- Forwarded message ---------
>>> From: Yahoo Groups Notification <
>>> [hidden email]>
>>> Date: Wed, Jan 8, 2020 at 9:32 AM
>>> Subject: MODERATE -- [hidden email] posted to belajar-excel
>>> To: <[hidden email]>
>>>
>>>
>>>
>>> Hello,
>>>
>>> A message has been sent to the belajar-excel group from
>>>
>>>   [hidden email]
>>>
>>> A complete copy of this message has been attached for your convenience.
>>>
>>>
>>> ---------- Forwarded message ----------
>>> From: halak hita <[hidden email]>
>>> To: [hidden email] <belajar-excel@yahoogroups..com>
>>> Cc:
>>> Bcc:
>>> Date: Wed, 8 Jan 2020 09:31:35 +0700
>>> Subject: Formula pemisahan code
>>> Dear master excel
>>> Tolong dibantu apa formula ambil code id seperti terlampir.
>>>
>>>
>>>
>>> Terima kasih
>>>
>>> Halkit
>>>
>>>
>
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: MODERATE -- halakhita1983@gmail.com posted to belajar-excel

hendrik karnadi
In reply to this post by halak hita
coba fungsi kombinasiLeft, Len , Find dan Right.
Salam,HK

Sent from Yahoo Mail on Android
 
  On Sat, Jan 11, 2020 at 20:05, halak hita [hidden email] [belajar-excel]<[hidden email]> wrote:      

Terima kasih mr.kidNamun bagaimana FORMULA nya jika text di PN tidak standar atau tidak selalu 15 sebelum tanda "-".Sebab PN ini bervariasi jumlah karakternya..
Misalnya
SC12345-R1-MAUL RT1234-R1-MODV0123456789-WEWV123456-MAULZA636721-1-MODEZA7200891600-R2-DELR123456778901-R10-SAZHA0003195008-RENZA6443800702-ZR78008002400-ERT8000A2200-01-DT
Bagaimana Formulanya agar PN bisa diambil sebelum tanda "-" terakhir
Hasil yg diinginkanSC12345-R178008002400800A2200-01



Pada tanggal Kam, 9 Jan 2020 18:33, 'Mr. Kid' [hidden email] [belajar-excel] <[hidden email]> menulis:

    

PN= Left( teks , 15 )Brand= Mid( teks , 17 , 99 )


On Thu, Jan 9, 2020 at 12:01 AM Mr. Kid <[hidden email]> wrote:



---------- Forwarded message ---------
From: Yahoo Groups Notification <[hidden email]>
Date: Wed, Jan 8, 2020 at 9:32 AM
Subject: MODERATE -- [hidden email] posted to belajar-excel
To: <[hidden email]>



Hello,

A message has been sent to the belajar-excel group from

  [hidden email]

A complete copy of this message has been attached for your convenience.


---------- Forwarded message ----------
From: halak hita <[hidden email]>
To: [hidden email]
Cc: 
Bcc: 
Date: Wed, 8 Jan 2020 09:31:35 +0700
Subject: Formula pemisahan code
Dear master excelTolong dibantu apa formula ambil code id seperti terlampir..


Terima kasih
Halkit

   
  #yiv5730363515 #yiv5730363515 -- #yiv5730363515ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5730363515 #yiv5730363515ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5730363515 #yiv5730363515ygrp-mkp #yiv5730363515hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5730363515 #yiv5730363515ygrp-mkp #yiv5730363515ads {margin-bottom:10px;}#yiv5730363515 #yiv5730363515ygrp-mkp .yiv5730363515ad {padding:0 0;}#yiv5730363515 #yiv5730363515ygrp-mkp .yiv5730363515ad p {margin:0;}#yiv5730363515 #yiv5730363515ygrp-mkp .yiv5730363515ad a {color:#0000ff;text-decoration:none;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ygrp-lc {font-family:Arial;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ygrp-lc #yiv5730363515hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ygrp-lc .yiv5730363515ad {margin-bottom:10px;padding:0 0;}#yiv5730363515 #yiv5730363515actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5730363515 #yiv5730363515activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5730363515 #yiv5730363515activity span {font-weight:700;}#yiv5730363515 #yiv5730363515activity span:first-child {text-transform:uppercase;}#yiv5730363515 #yiv5730363515activity span a {color:#5085b6;text-decoration:none;}#yiv5730363515 #yiv5730363515activity span span {color:#ff7900;}#yiv5730363515 #yiv5730363515activity span .yiv5730363515underline {text-decoration:underline;}#yiv5730363515 .yiv5730363515attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5730363515 .yiv5730363515attach div a {text-decoration:none;}#yiv5730363515 .yiv5730363515attach img {border:none;padding-right:5px;}#yiv5730363515 .yiv5730363515attach label {display:block;margin-bottom:5px;}#yiv5730363515 .yiv5730363515attach label a {text-decoration:none;}#yiv5730363515 blockquote {margin:0 0 0 4px;}#yiv5730363515 .yiv5730363515bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5730363515 .yiv5730363515bold a {text-decoration:none;}#yiv5730363515 dd.yiv5730363515last p a {font-family:Verdana;font-weight:700;}#yiv5730363515 dd.yiv5730363515last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5730363515 dd.yiv5730363515last p span.yiv5730363515yshortcuts {margin-right:0;}#yiv5730363515 div.yiv5730363515attach-table div div a {text-decoration:none;}#yiv5730363515 div.yiv5730363515attach-table {width:400px;}#yiv5730363515 div.yiv5730363515file-title a, #yiv5730363515 div.yiv5730363515file-title a:active, #yiv5730363515 div.yiv5730363515file-title a:hover, #yiv5730363515 div.yiv5730363515file-title a:visited {text-decoration:none;}#yiv5730363515 div.yiv5730363515photo-title a, #yiv5730363515 div.yiv5730363515photo-title a:active, #yiv5730363515 div.yiv5730363515photo-title a:hover, #yiv5730363515 div.yiv5730363515photo-title a:visited {text-decoration:none;}#yiv5730363515 div#yiv5730363515ygrp-mlmsg #yiv5730363515ygrp-msg p a span.yiv5730363515yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5730363515 .yiv5730363515green {color:#628c2a;}#yiv5730363515 .yiv5730363515MsoNormal {margin:0 0 0 0;}#yiv5730363515 o {font-size:0;}#yiv5730363515 #yiv5730363515photos div {float:left;width:72px;}#yiv5730363515 #yiv5730363515photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv5730363515 #yiv5730363515photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5730363515 #yiv5730363515reco-category {font-size:77%;}#yiv5730363515 #yiv5730363515reco-desc {font-size:77%;}#yiv5730363515 .yiv5730363515replbq {margin:4px;}#yiv5730363515 #yiv5730363515ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5730363515 #yiv5730363515ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5730363515 #yiv5730363515ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5730363515 #yiv5730363515ygrp-mlmsg select, #yiv5730363515 input, #yiv5730363515 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5730363515 #yiv5730363515ygrp-mlmsg pre, #yiv5730363515 code {font:115% monospace;}#yiv5730363515 #yiv5730363515ygrp-mlmsg * {line-height:1.22em;}#yiv5730363515 #yiv5730363515ygrp-mlmsg #yiv5730363515logo {padding-bottom:10px;}#yiv5730363515 #yiv5730363515ygrp-msg p a {font-family:Verdana;}#yiv5730363515 #yiv5730363515ygrp-msg p#yiv5730363515attach-count span {color:#1E66AE;font-weight:700;}#yiv5730363515 #yiv5730363515ygrp-reco #yiv5730363515reco-head {color:#ff7900;font-weight:700;}#yiv5730363515 #yiv5730363515ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ov li a {font-size:130%;text-decoration:none;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5730363515 #yiv5730363515ygrp-sponsor #yiv5730363515ov ul {margin:0;padding:0 0 0 8px;}#yiv5730363515 #yiv5730363515ygrp-text {font-family:Georgia;}#yiv5730363515 #yiv5730363515ygrp-text p {margin:0 0 1em 0;}#yiv5730363515 #yiv5730363515ygrp-text tt {font-size:120%;}#yiv5730363515 #yiv5730363515ygrp-vital ul li:last-child {border-right:none !important;}#yiv5730363515