Excel Magic Trick 316: Concatenate Selection w Criteria (MoreFunc Excel Add-in functions)

[ledeviltaz]

Hello I have students and I need to make them an Id number.

I have the following cells Date of course; the # of students per class; and day or night.

so on an example a student who is registered for the 30 janvier 2012 he is the 3 student of the class and he is register for night I would like his student number to be : 3001201203N

30 the day

01 the month

2012 the year

03 student #

N night or D for day

Hope I am clear...

Thanks for all your videos they are extremely educating and fun

[ledeviltaz]

@ledeviltaz Thanks through your ammazing videos I found what I was looking for.:

=text(a1;"ddmmyyy")&text(a2;"0­0")&a3)

Actually just reallized I still need help... How do I go from Day to just D or From Night to just N

Thanks for your help

[ExcelIsFun]

@ledeviltaz , try:

=TEXT(A2,"ddmmyyy")&TEXT(B2,"0­0")&LEFT(E2,1)

[ExcelIsFun]

@ledeviltaz , maybe:

=TEXT(A2,"ddmmyyy")&TEXT(B2,"0­0")&LEFT(E2,1)

[ledeviltaz]

@ExcelIsFun Thanks works like a charm

[MrHelal123]

Hi Mike, how would i be able to concatenate using the same examples if i didn't have the mconcat add in (say in excel 2003)?

[ExcelIsFun]

I have zero idea. VBA, but I don't know how to do it.

[Mynotoar]

Must say though, this one was confusing, didn't understand a lot of it :P.

[ExcelIsFun]

Yes, some of the advanced videos that I post are confusing if you are not familiar with all the parts of the formula or trick. But I have two great series that can get you up to speed:

Excel Basics Series

and

Highline Excel Class Series.

Search for and watch this video to see how to find these videos and the free workbooks with lots of notes:

Search For Excel Videos Download Excel Files excelisfun

[YUSUFFROSH]

Thanks Mike that was great ... you are the excel God ...

[ExcelIsFun]

Dear YUSUFFROSH ,

You are welcome! But I an no god, I am just a guy having fun with Excel!

--excelisfun

[Mynotoar]

No, I agree with him, you ARE the number one Excel God.

[ExcelIsFun]

No, I can't be an Excel god, because there are many people that know WAY more than i do about Excel! I AM just a guy having fun with Excel who like to share videos about how much fun I am having with Excel!!!