Excel Left, Right, and Mid Functions

24 04 2010

Excel Left, Right, and Mid Functions

Article by Tricia Goss (15,913 pts )
Edited & published by Michele McDonough (86,084 pts ) on Apr 12, 2010

Excel is a great application for importing data. Sometimes, though, imported text does not appear just as you would like. Read on to learn how the LEFT, RIGHT and MID functions can help you resolve these issues.

// //

Isolating Parts of an Excel Cell

Excel’s LEFT, RIGHT and MID functions make it simple for you to slice up and use cell contents in other cells of a spreadsheet. For instance, perhaps you have imported a text file into Excel using .csv file format. (If you are unfamiliar with this process, check out the BrightHub tutorial How to Import CSV Files.) You can then use that text to create a database.

Reformatting Excel Data

If you have a large text file in an Excel column that you need broken up and used in other cells, the LEFT, RIGHT and MID functions will make light work of it. This is especially true if all of the text is the same length.

This is because the syntaxes for LEFT, RIGHT and MID are as follows:

LEFT(text,num_chars)

RIGHT(text,num_chars)

MID(text,start_num,num_chars)

What this means is that Excel will count the number of characters you specify to “cut” and use the text in the cells to which the function is applied.

For example, we have a text list with numbers in ten-digit phone number format. We want to break down the list into an area code column, a prefix column, and a suffix column.

Article Image

To begin, we will select the first cell in the Area Code column. In the Formula Bar, we will enter =LEFT( and Excel will prompt us for the text and number of characters. We know we want to draw the text from cell B4, and we also know we only want to use the first three characters from the left side of that cell. Therefore, the formula we enter will be =LEFT(B4,3).

Article Image

Next, we want to extract text from the first column and fill in the Prefix column. To begin, we will select the first cell in the Prefix column and go to the Formula Bar. Enter =MID( and Excel prompts us for the text, start number and number of characters we want to use. We know we want to extract the text from cell B4. We know the start number is “5” because we want to start with the fifth character (we don’t want to use the dash). Finally, we know that we want to extract three characters. Therefore, the formula for this cell will be =MID(B4,5,3).

Article Image

Finally, we want to extract the suffix from the text column. Just as with the previous instances, we’ll select the first cell and enter =RIGHT( into the Formula Bar. Excel will prompt us for the text source and the number of characters we want. For this cell, the formula will be =RIGHT(B4,4).

Once we have the formulas entered into each of the initial cells, we can select those three, grab the fill handle, and copy the formulas all the way down.

Article Image

If you have a different situation and need to combine text from different columns, check out this Bright Hub tutorial, Use Excel’s CONCATENATE Function to Combine Cells. Also, if you’re looking for more tips and tricks, be sure to take a look at the other Microsoft Excel function tutorials available.





Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data in Excel

24 04 2010

Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data in Excel

Use Ctrl+Shift+Enter (CSE formulas) to supercharge your formulas in Excel! Yes, it is true…there is a secret class of formulas in Excel. If you know the magic three keys, you can get a single Excel array formula to replace thousands of other formulas.

95% of Excel users do not know about CSE Formulas. When most people hear their real name, they think “That doesn’t sound the least bit useful” and never bother to learn about them. If you think SumIf and CountIf are cool, you will soon discover that Ctrl+Shift+Enter (CSE) formulas will run circles around SumIf and CountIf. CSE formulas allow you to literally replace 1000′s of cells of formulas with a single formula. Yes, my fellow Excel Guru’s, there is something this powerful sitting right under our noses and we never use it.

Before there was SumIf, you could use a CSE formula to do the same thing as SumIf. Microsoft gave us SumIf and CountIf, but CSE formulas are not obsolete. Oh no, and they can do much more! What about if you want to do AverageIf? How about GrowthIf? AveDevIf? Even MultiplyByPiAndTakeTheSquareRootIf. Just about anything you can imagine can be done with one of these CSE formulas.

Here is why I think CSE formulas never caught on. First, their real name scares everybody away. Second, they require a foreign, counter-intuitive handling in order to make them work. After you type in a CSE formula, you cannot just hit Enter. You cannot just exit the cell with a click of an arrow key. Even if you get the formula right and hit the enter key, Excel gives you the totally non-user friendly “VALUE!” error. It doesn’t say, “Wow – that is beautiful. You are 99.1% of the way there,” which you probably were.

Here is the secret: After you type a CSE formula, you have to hold down the Ctrl and the Shift keys, and then hit Enter. Grab a sticky note and write that down: Ctrl Shift Enter. Power Excel users will have the opportunity to use these formulas about once a month. If you don’t write Ctrl Shift Enter down right now, you will forget it by the time something comes up again.

AverageIf Using CSE Formula

Examine this example: Say you wanted to average just the values in column C where column A was in the East region.

The formula in cell A13 is an example of a CSE formula.

=AVERAGE(IF(A2:A10=”East”,C2:C10))

Plug this in and you will get 7452.667, the average of just the East values. Cool? You just created your own version of the nonexistent Excel function AverageIf. Remember: Hit Ctrl+Shift+Enter to enter the formula.

Check out the next example below.

Parameterized CSE Formula

In this example, we make the CSE formula more general. Rather than specifying that we are looking for “East,” indicate you want whatever value is in A13. The formula is now =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).

Oddly enough, Excel will let you copy and paste CSE formulas without any special keystrokes. I copied C13 to C14:C15 and I now have averages for all of the regions.

Calculating Sum of Range Multiplication

Our mainframe system stores Quantity and Unit Price, but not the extended price. Sure, it is easy enough to add a column C and fill it with =A2*B2 and then total column C, but you don’t have to!

Here, our CSE formula is =SUM(A2:A10*B2:B10). It takes each cell in A2:A10, multiplies by the corresponding cell in B2:B10 and totals the result. Type the formula, Hold down Ctrl and Shift while you hit enter, and you have you answer in one formula instead of 10.

Do you see how powerful this is? Even if I had 10,000 rows of data, Excel will take this single formula, do the 10,000 multiplications and give me the result.

OK, here are the rules: You have to hit Ctrl+Shift+Enter anytime you enter or edit these formulas. Failure to do so results in the totally ambiguous #VALUE! error. If you have multiple ranges, they all have to have the same general shape. If you have a range mixed with single cells, the single cells will be “replicated” in memory to match the shape of your range.

After you successfully enter one of these and look at it in the formula bar, you should have curly braces around the formula. You never enter the curly braces yourself. Hitting Ctrl+Shift+Enter puts them there.

These formulas are tough to master. MrExcel gets a headache just thinking about them. If I have a tough one to enter, I go to Tools>Wizards>Conditional Sum wizard and walk through the dialog boxes. The output of the conditional sum wizard is a CSE formula, so this usually can give me enough hints on how to enter what I really need.

Did you have to take BASIC in 11th grade with Mr. Irwin? Or, worse yet, did you get a “D” in linear algebra with Mrs. Duchess in college? If so, you are in good company and will shudder whenever you hear the word “array.” – I run screaming in the other direction whenever anyone says array. I understand them, but this is Excel, I don’t need arrays here!. The evil secret is that Excel and Microsoft call these formulas “array formulas”. Stop — don’t run away. It is OK, really. MrExcel has renamed them CSE formulas because it sounds less scary, and because the name helps you remember how to enter them — Ctrl Shift Enter. I only mention the real name here in case you run into someone from Microsoft who never had Mrs. Duchess for linear algebra, or, in case you decide to peruse the Help files. If you go to help, search under the evil alias of “array formula,” but between us friends, let’s call them CSE — OK?





rumus left dan right

24 04 2010

Dalam pengolahan data terkadang kita hanya ingin mengetahui awal / akhir karakter dari suatu teks ataupun karakter yang berada didalam teks. Untuk itu kita menggunakan rumus LEFT, RIGHT, ataupun MID.

1. Fungsi LEFT digunakan untuk mengambil beberapa karakter dengan posisi sebelah KIRI dari suatu teks.
Dengan formula : =LEFT(TEXT,NUM_CHARS)

TEXT berisi data teks yang terdiri dari beberapa karakter (misal Nominal, Kalimat dan Kata) / ataupun alamat cell
NUM_CHARS berisi angka yang menunjukkan jumlah karakter yang hendak diambil dari data teks ataupun alamat cell

2. Fungsi RIGHT digunakan untuk mengambil beberapa karakter dengan posisi sebelah KANAN dari suatu teks.
Dengan formula : =RIGHT(TEXT,NUM_CHARS)

3. Fungsi MID digunakan untuk mengambil beberapa karakter dengan posisi BERADA DIANTARA dari suatu teks.
Dengan formula : =MID(TEXT,START_NUM,NUM_CHARS)

START_NUM berisi angka yang menunjukkan awal karakter yang hendak diambil dari data teks ataupun alamat cell

Misal :

1.Fungsi LEFT
Saya mencontohkan bagaimana hasil dari sebuah kata dan nominal dengan menjalankan rumus ini.
Catatan : Untuk setiap 01 karakter pada suatu kata / angka baik itu angka 0 (nol) maupun spasi “ “ akan dianggap 01 karakter.

pada kolom A5 diketik “MOBIL”, dan pada kolom B5 diketik “2″ maka hasilnya pada kolom C5 dengan rumus : “=LEFT(A5,B5)” adalah “MO”
Artinya diambil 2 karakter sebelah kiri dari kata “MOBIL” yakni “MO”

2. Fungsi RIGHT

pada kolom A5 diketik “MOBIL”, dan pada kolom B5 diketik “2″ maka hasilnya pada kolom C5 dengan rumus : “=RIGHT(A5,B5)” adalah “IL”
Artinya diambil 2 karakter sebelah kiri dari kata “MOBIL” yakni “IL”

3. Fungsi MID

pada kolom A5 diketik “MOBIL”, pada kolom B5 diketik “2″ dan kolom C5 diketik “3″ maka hasilnya pada kolom D5 dengan rumus : “=MID(A5,B5,C5)” adalah “OBI”
Artinya diambil 3 karakter sebelah kiri dimulai dari karakter ke-2 dari kata “MOBIL” yakni “OBI”





rumus vlookup

24 04 2010

Fungsi excel VLookup “=VLOOKUP()” Pada bagian ini kami akan mengulas secara detail fungsi excel VLOOKUP. kelebihan fungsi excel VLOOKUP dibanding dengan LOOKUP yaitu, pengambilan data dengan VLOOKUP bisa disetting mutlak (Nilai Range_Lookup diisi false), artinya jika sumber data tidak ada maka vlookup akan menghasilkan nilai #N/A yang artinya “no value is available” tidak ada nilai yang tepat. Hal ini sangat menolong kita supaya tidak terjadi kesalahan dalam pengambilan data. Lagi pula fungsi excel VLOOKUP dapat mengambil data kolom ke n yang sebaris dengan mensettingg Col_index_num. * Lookup_value Nilai apa yang akan kita cari, contoh di bawah, nilai dengan No Induk 99999 * Table_array Table dimana tempat data yang akan dicari * Col_index_num Kolom yang keberapa yang akan dicari Nilai dari Col_index_num tidak boleh lebih dari jumlah kolom dari data Table_array, jika lebih akan menghasilkan nilai #REF * Range_lookup Diisi dengan true(1) atau false(0) Jika diisi dengan true(1), Table_array harus disusun secara urut, oleh sebab itu sebagai amannya menghindari salah mencari data pakai nilai false(0) Contoh di atas memakai nilai false, jika nilai yang kita cari tidak menemukan dari Table_array, nilai hasil akan menjadi #N/A Gambar 01 Rumus Excel Vlookup Contoh Penggunaan VLookup #Kolom ke N Pada contoh di bawah ini bagaimana cara mengambil data pada kolom ke n setelah No Induk. Pada gambar 02 menunjukkan pengambilan data dengan kunci No Induk dari data sebelah kiri. No Induk “90123″ akan menghasilkan Nama “Uban” jika diambil kolom ke 2, begitu juga jika pengambilan data untuk kolom ke 3 akan mengambil nilai dari Kelamin. Untuk setting pengambilan data kolom ke n tersebut bisa dilihat pada gambar 03. Gambar 02 Contoh Rumus excel VLookup Gambar 03 Contoh formula rumus excel vlookup Salah satu trik (Gambar 04) yang sering kami pakai dalam pengambilan sebuah data yang banyak dengan menggunakan rumus excel vlookup. Pada saat mengambil data, kita juga perlu cross check apakah data itu semua terambil. Contoh kasus pada gambar (Gambar 01) di bawah, data bedara di sebelah kiri, pengambilan data sebelah kanan. Pada bagian sebelah kiri bagian data tersebut, kami buatkan juga rumus excel VLookup mengambil data dari sebelah kanan. Jika hasil pengambilan dari sebelah kanan tida ada yang sesuai, maka kolom check (sebelah kiri) akan menghasilkan nilai #N/A. Gambar 04 Contoh rumus excel vlookup





fungsi hlookup

6 04 2010

Fungsi Hlookup sederhana

Fungsi Hlookup hampir sama dengan vlooukup, tetapi tabel referensinya disusun secara horisontal.

Berikut contoh penggunaan hlookup

1. Buat tabel kerja dan tabel referensi

2. Masukkan formula berikut:
a. Pada sel B5 ketik:
=HLOOKUP(A5,$F$4:$I$6,2,0)
b. Pada sel C5 ketik:
=HLOOKUP(A5,$F$4:$I$6,3,0)
c. Copy formula dari baris 5 ke 16
3. Hasilnya seperti gambar di bawah





ms excel

6 04 2010

Contoh Kasus Vlookup misal Hotel Jika menggunakan tabel bantu untuk kasus hotel misal seperti ini Untuk table Bantu saya beri nama tbl Hargahotel Dan kasus seperti ini untuk mencari laporan pendapatan hotel hotel1 Untuk memasukan data Kelas Kamar, Lama Inap, harga dan total menggunakan rumus sebagai berikut : * Kelas kamar ditentukan dari No Struk 2 kode di tengah yaitu BS, EX, SD pada urutan 5 dan 6 dengan menggunakan table bantu Harga. Jawabnya : Untuk menyelesaikan Kelas Kamar saya menggunakan table bantu Harga dan menggunakan fungsi Vlookup dan mid (untuk mengambil kode kamar). Untuk rumusnya sebagai berikut : =VLOOKUP(MID(B2;5;2);$I$8:$K$10;2) * Untuk mencari harga kamar juga ditentukan dari No Struk 2 kode di tengah yaitu BS, EX, SD pada urutan 5 dan 6 dengan menggunakan table bantu Harga. jawab Untuk menyelesaikan Harga Kamar saya menggunakan table bantu Harga dan menggunakan fungsi Vlookup dan mid (untuk mengambil kode kamar). Untuk rumusnya sebagai berikut : =VLOOKUP(MID(B2;5;2);$I$8:$K$10;3) * Untuk mencari Total harga = Lama inap * Harga Jawab = E2 * F2 Dan hasil akhirnya sebagai berikut hotel2 Semoga contoh ini bisa membantu untuk belajar excel secara mandiri Relasi Topik Fungsi penggunaan teks (LEFT, RIGHT, MID) Fungsi Pembacaan tabel Vlookup Ditulis dalam MS Excel | Tag: contoh kasus excel, Mid, Vlookup








Ikuti

Get every new post delivered to your Inbox.