[belajar-excel] Plat Kendaraan

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

[belajar-excel] Plat Kendaraan

Andrie -
Dear all,
bagaimana sih caranya membuat format cell plat kenderaan
ex : B1234AC = B-1234-AC
       BA1234AC = BA-1234-AC

tq
Reply | Threaded
Open this post in threaded view
|

Bls: [belajar-excel] Plat Kendaraan

ghozi alkatiri
misalkan data ada di A3 maka formula (formula array di enter dengan menahan tobol Ctrl +Shift)
{=SUBSTITUTE($A3;MID($A3;MIN(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10)));COUNT(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10))));"-"&MID($A3;MIN(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10)));COUNT(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10))))&"-")}

penjelasan langkah ada di attachment

wassalam

Ghozi Alkatiri




________________________________
 Dari: Andrie - <[hidden email]>
Kepada: [hidden email]
Dikirim: Minggu, 11 November 2012 17:05
Judul: [belajar-excel] Plat Kendaraan
 

 
Dear all,
bagaimana sih caranya membuat format cell plat kenderaan
ex : B1234AC = B-1234-AC
       BA1234AC = BA-1234-AC

tq
 
 

ghozi 11112012 Plat kendarann.xlsx (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Plat Kendaraan

Zainul Ulum
Coba juga, tanpa formula array
=SUBSTITUTE(A3,-LOOKUP(1,-MID(A3,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A3&"0123456789")),{1;2;3;4})),
"-"&-LOOKUP(1,-MID(A3,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A3&"0123456789")),{1;2;3;4}))&"-")

From: [hidden email] [mailto:[hidden email]] On Behalf Of ghozi alkatiri
Sent: Sunday, November 11, 2012 9:16 PM
To: [hidden email]
Subject: Bls: [belajar-excel] Plat Kendaraan

 
misalkan data ada di A3 maka formula (formula array di enter dengan menahan tobol Ctrl +Shift)
{=SUBSTITUTE($A3;MID($A3;MIN(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10)));COUNT(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10))));"-"&MID($A3;MIN(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10)));COUNT(IF(ISNUMBER(--MID($A3;ROW($1:$10);1));ROW($1:$10))))&"-")}

penjelasan langkah ada di attachment

wassalam

Ghozi Alkatiri


________________________________________
Dari: Andrie - <[hidden email]>
Kepada: [hidden email]
Dikirim: Minggu, 11 November 2012 17:05
Judul: [belajar-excel] Plat Kendaraan

 
Dear all,
bagaimana sih caranya membuat format cell plat kenderaan
ex : B1234AC = B-1234-AC
       BA1234AC = BA-1234-AC

tq



Plat kendarann.xlsx (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: Plat Kendaraan

summonery
Administrator
In reply to this post by Andrie -
Coba dengan formula,
=REPLACE(REPLACE(B2,MATCH(1,INDEX(-MID(B2,ROW($1:$8),1),))+1,,"-"),
3-(MID(B2,2,1)<"A"),,"-")(Kasus memisahkan kode nomor kendaraan bermotor
(nomor polisi) dengan tanda "-" belum lama ini juga pernah muncul di
milis tetangga)

>semoga bermanfaat

--- In [hidden email], Andrie - <sioean2@...> wrote:
>
> Dear all,
> bagaimana sih caranya membuat format cell plat kenderaan
> ex : B1234AC = B-1234-AC
>        BA1234AC = BA-1234-AC
>
> tq
>