Office Excel – TEXTJOIN Function

Microsoft releases new iterations of Microsoft Office suite every three years for desktop and Microsoft Office 365 is the subscription based cloud version of their Office software. Earlier in 2016, Microsoft released the 2016 edition of Office and some updates to Office 365 which added new features in Excel. Some very useful functions like the CONCAT and TEXTJOIN functions are added which make concatenating or joining text very easier with multiple cells or strings in your spreadsheet. These functions are only available in the latest Office 2016 desktop installation and Office 365 subscription. To show these new functions, here is the Excel CONCAT and TEXTJOIN function tutorial. I am breaking them into 2 different tutorials for ease of access.

The Excel TEXTJOIN function joins or combines text from multiple cells in your spreadsheet with each string separated by a delimiter. The delimiter can be a comma or space. If the delimiter is empty, the Excel TEXTJOIN function will concatenate the strings like in the previous tutorial. Here is how to use Excel TEXTJOIN function tutorial:

The format of the Excel TEXTJOIN function is:
TEXTJOIN(delimiter, ignore_empty, text1, text2, … , textN)

Definition

  • “delimiter” is the character or string inserted between each string you want to join.
  • “ignore_empty” is either TRUE (exclude empty cells or strings) or FALSE (include empty cells or strings).
  • “text1” is the first string or cell and “textN” is the nth string or cell which you want to join.

Now here is how we use the Excel TEXTJOIN function:

  1. In your Excel spreadsheet, see which cells or strings you want to join using TEXTJOIN.
  2. Then select a cell where you want to display the result of the TEXTJOIN function.
  3. For example, in this following example spreadsheet, we want to join the strings in cells A2 through D2 and we want to display the output in cell F2.
  4. Select the cell F2 and enter the Excel TEXTJOIN function in the formula bar above the spreadsheet:
  5. =TEXTJOIN(“ ”,TRUE,A2,B2,C2,D2)

  6. After entering the function, you will see the result in cell F2:
  7. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  8. As you can see in the example, the data in cells A2 through D2 is now joined in cell F2 and there are spaces between the strings since we entered space as a delimiter in the function.
  9. You can do this with numbers as well, like in the result shown in cell F3:
  10. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  11. The result in cell F4 is when TRUE is used:
  12. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  13. The result in cell F5 is when FALSE is used and you can see the difference between step 8 and 9:
  14. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  15. You can also add strings directly in the function as shown in the following examples:
  16. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

—–
You can view more Office Excel Tutorials in the link too!

Quick note on Web.config file

A quick note on Web.config file in the ASP.NET project. It is worth noting that the file is a XML File (read this for more info – https://msdn.microsoft.com/en-us/library/ff400235.aspx )

One issue that hindered my process earlier was that the content in the value has special character it in, particularly, one of my SQL server’s password has a special character in it.

I would have to go and replace them accordingly. For instance, my password is <Password1& (note that < and ” is inclusive), i would have to change it to
&lt;Password1&amp;

You can refer to the list here – https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

Office Excel – CONCAT Function

Microsoft releases new iterations of Microsoft Office suite every three years for desktop and Microsoft Office 365 is the subscription based cloud version of their Office software. Earlier in 2016, Microsoft released the 2016 edition of Office and some updates to Office 365 which added new features in Excel. Some very useful functions like the CONCAT and TEXTJOIN functions are added which make concatenating or joining text very easier with multiple cells or strings in your spreadsheet. These functions are only available in the latest Office 2016 desktop installation and Office 365 subscription. To show these new functions, here is the Excel CONCAT and TEXTJOIN function tutorial. I am breaking them into 2 different tutorials for ease of access.

Through the Office Excel CONCAT function, you can connect two or more text in different cells in your spreadsheet. When you connect two text, they are just joined together with no separation or space between them. Here is the tutorial on how to use Excel CONCAT function:

The format of the Excel CONCAT function is:
CONCAT(text1, text2, … , textN)

Where “text1” is the first string or cell and “textN” is the nth string or cell which you want to concatenate.

  1. In your Excel spreadsheet, see which cells or strings you want to concatenate.
  2. Then select a cell where you want to display the concatenated text.
  3. For example, in this following example spreadsheet, we want to concatenate the text in cells A11 through D11 and we want to display the output in cell F11.
  4. Select the cell F11 and enter the CONCAT function in the formula bar above the spreadsheet:
  5. =CONCAT(A11,B11,C11,D11)

  6. After entering the function, you will see the result in cell F11:
  7. Excel Concat

    Excel Concat

  8. As you can see in the example, the data in cells A11 through D11 is now concatenated in cell F11 and there are no spaces between the text.
  9. You can do this with numbers as well, like in the result shown in cell F12:
  10. Excel Concat

    Excel Concat

  11. If you enter a cell in the function which is empty, the function will not show anything from that cell in the output:
  12. Excel Concat

    Excel Concat

  13. You can also directly add a string in the function, as shown in the following example:
  14. Excel Concat

    Excel Concat

—–
You can view more Office Excel Tutorials in the link too!

Teaching Assistant – IS3261 (MOBILE APPS DEVELOPMENT FOR ENTERPRISE)

This semester, Steven and myself have decided to take up the challenge to be the Teaching Assistant for the module, NUS IS3261 – MOBILE APPS DEVELOPMENT FOR ENTERPRISE.

It is definitely a challenge as this is a level 3000 module that teaches programming on Android Studio. We all know that the Android ecosystem changes very often and we will have to keep active eye and ear to keep up with the pace of the release.

If anyone stumble upon my page when finding tips for the module, please feel free to contact me via my contact form or comment below, i would reply to you privately.

NUS Year 4 Semester 1

Semester 1, AY 13/14
1.MA1301 – INTRODUCTORY MATHEMATICS (By Dr. Wang Fei)
2.CS1020 – DATA STRUCTURES AND ALGORITHMS I (By A.Prof Tan Sun Teck)
3.CS2100 – COMPUTER ORGANISATION (By A.Prof Wong Weng Fai)*
4.IS1105 – STRATEGIC IT APPLICATIONS (By Dr Guo Xiaojia)
5.SSA2209 – GOVERNMENT AND POLITICS OF SINGAPORE (By A.Prof Bilveer Singh)
*I withdrew from the module in week 7.

Semester 2, AY 13/14
1. CS2100 – COMPUTER ORGANISATION (By Dr. Aaron Tan, Dr. Soo Yuen Jie)
2. FMC1201 – FRESHMAN SEMINAR: IS COMPUTER SCIENCE SCIENCE (By Prof Tay Yong Chiang)
3. CS1231 – DISCRETE STRUCTURES (By A.Prof Tay Tiong Seng)
4. IS2102 – REQUIREMENTS ANALYSIS AND DESIGN (By Prof Kisenchand Nathumal Ranai)
5. GEK1531 – INTRODUCTION TO CYBERCRIME (By Prof Lam Kwok Yan and A. Prof Leung Ka Hin)

Semester 1, AY 14/15
1. ES1102 – ENGLISH FOR ACADEMIC PURPOSES (By Wong Waa Bee)
2. CS2102 – DATABASE SYSTEMS (By Prof Lee Mong Li Janice and Visiting Prof Shen Heng Tao from UQ)
3. IS3261 – MOBILE APPS DEVELOPMENT FOR ENTERPRISE (By A.Prof Ng Teck Khim)
4. IS3101 – MANAGEMENT OF INFORMATION SYSTEMS (By Prof John Lim)
5. ACC1002X – FINANCIAL ACCOUNTING (By A.Prof Ma Guang)

Semester 2, AY 14/15
1. MA1312 – CALCULUS WITH APPLICATIONS (By Prof Ng Wee Seng)
2. IS2104 – SOFTWARE TEAM DYNAMICS (By A.Prof Sharon Tan)
3. IS2103 – ENTERPIRSE SYSTEMS DEVELOPMENT CONCEPTS (By A.Prof Danny Poo)
4. IS3242 – SOFTWARE QUALITY MANAGEMENT (By. Raymond Ching)

Semester 1, AY 15/16
1. IS3102 – ENTERPIRSE SYSTEMS DEVELOPMENT PROJECT (By Dr. Tan Wee Kek)
2. CS2105 – INTRODUCTION OF COMPUTER NETWORKS (By Dr. Leong Wai Kay)
3. IS4225 – STRATEGIC IS PLANNING (By TK Teo)
4. NM3210 – CYBERCRIME AND SOCIETY (By A.Prof Morales Tellez Sofia Rosario)

Semester 2, AY 15/16
1. IS3220 – SERVICE DESIGN AND INNOVATION (By A.Prof Benjamin Lian Yin Hon)
2. IS3251– PRINCIPLES OF TECHNOLOGY ENTREPRENEURSHIP (By Dr. Francis Yeoh)
3. IS4100 – IT PROJECT MANAGEMENT (By A.Prof Hahn Jungpil)
4. IS4243 – INFORMATION SYSTEMS CONSULTING (By Prof Teo Hock Hai)
5. NM3238 – SOFTWARE STUDIES (By A.Prof Wyse, Lonce Lamar)

Semester 1, AY 16/17
1. IS4204 – IT GOVERNANCE (By Prof Alex Siow Yuen Khong)
2. IS4224 – SERVICE SYSTEMS (By Dr. OH Lih Bin)
3. IS4231 – INFORMATION SECURITY MANAGEMENT (By A.Prof Benjamin Lian)
4. IS4241 – SOCIAL MEDIA NETWORK ANALYSIS (By A.Prof Chan Hock Chuan)
5. ST2334 – PROBABILITY AND STATISTICS (By Dr. David Chew)