How to concatenate in cell

Tag: excel Author: deerhui1200 Date: 2009-09-14

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.

Other Answer1

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)

comments:

Thank you indeed .I am grateful to this kind of community/network .This will help me for next project.Thank you once again.

Other Answer2

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

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

comments:

Thank you indeed .I am grateful to this kind of community/network .This will help me for next project.Thank you once again.

Other Answer3

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.

comments:

Thank you indeed .I am grateful to this kind of community/network .This will help me for next project.Thank you once again.