How to concatenate in cell

Question

I want to concatenate two or more cell but i don't want to concatenate whole cell .Only want to take 2 or more character of 1st cell and 2 or more character of 2nd cell then concatenate them in the 3rd cell.Like..

Student Name Serial number Address Group Section Student ID

Monir 07001 Dhaka,Bangladesh Science B SC001B

I want to take last three digit from the Serial number cell, if it is science from the group cell i want to take only sc and if it is arts i want to take at from the group cell and from the section cell i want to take full character ,Finally concatenate into the Student ID cell.

How do i can do it. Please help me.

Answer 1

Use LEFT(), RIGHT() or MID() functions for extracting substrings. Use the "&" operator to concatenate. For example:

=RIGHT(C1,3) & LEFT(D1,2) & E1

Answer 2

Build up what you need slowly:

• The last three digits of Serial#: =RIGHT(B2, 3)
• Create another table that maps: Science to Sc, Art to At and then use the LOOKUP function. eg =LOOKUP(D2, X2:X6, Y2:Y6), Where column X is filled with "Science, Art, etc" and Y is filled with "Sc,At, etc"
• Concatenate is =CONCATENATE()

So the final answer would look something like:

=CONCATENATE(LOOKUP(D2, X2:X6, Y2:Y6), RIGHT(B2,3), E2)

Answer 3

Excel. Programming. Hmmm.

Anyhoo, I suppose you could use the following:

=LEFT(E2,2) & RIGHT(B2,3) & D2

That gets the first two characters of E2, last three characters from B2 and D2.