RIGHT, LEFT and MID functions

Another helpful ones if we need just piece of data from the cells.

	A	B
1	LR279JR	=RIGHT(A1,2)
2	FI698LK	=LEFT(A2,3)
3	ZS873NB	=MID(A2,2,3)

result

	B
1	JR
2	FI6
3	S87

=RIGHT(A1,2)
A1 means where are the data
2 means how many characters we want from right side
=LEFT(A2,3)
A2 means where are the data
3 means how many characters we want from left side
=MID(A3,2,3)
A3 means where are the data
2 means that we want to start from second character
3 means how many characters we want need

CONCATENATE function

This text function is very helpful if we need to join several words or text strings together.

	A	B	C
1	1	0	=CONCATENATE(A1,B1)
2	2	1	=CONCATENATE(A2,B2,"A")
3	3	2	=CONCATENATE(A3," ",B3,"A")

result

	C
1	10
2	21A
3	3 2A

do while / VBA

This is very simple example of using  do while. When you have some data in sheet1(named data) and you want to get results in sheet2 (named results) and the data is lying in A and B columns of sheet1.


data

	A	B
1	1	0
2	2	1
3	3	2

results

	A
1	1
2	1
3	1

Sub

Dim i as long

i=1

Do while Sheet1.Cells(i,1)<>""

Sheet2.Cells(i,1)= Sheet1.Cells(i,1)-Sheet1.Cells(i,2)

i=i+1

Loop

End Sub


When the sheets are named it is much easier to use these names. So let's see the same formula but this time the names are used.

Sub SubExample()

Dim i as long

i=1

Do while Sheets("data").Cells(i,1)<>""

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)-Sheets("data").Cells(i,2)

i=i+1

Loop

End Sub

Now let's change the sign and instead of "-" let's use "&".  We get similar results when we use CONCATENATE excel function.

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)& Sheets("data").Cells(i,2)

results

	A
1	10
2	21
3	32