Free Excel Tutorial for Engineers : String Functions

The functions thus far explained were some of the most basic functions that everyone uses. The following section deals with some advanced functions which make working with excel more fun.

CONCATENATE: The concatenate function allows you to join two or more text strings together. For example, if you have the X and Y coordinates in column C and D, then you can use the concatenate function to join both the X and Y Coordinates in the same column by using a slash (/) as a separator between the two. The formula to be used for the above example is: [=CONCATENATE(C3,”/”,D3)].

146.png

LEN: The length function allows you to find the length of the data in a cell or simple the number of characters in a cell. For example, if you have X Coordinate value, i.e., 549.509 in cell C3, then you can use the formula G1 [=LEN(C3)] to find the length of the data. This will give you the result as 7 in the destination cell.

147.png

MID: The Mid function allows you to extract only a part of the data from a particular cell. The syntax for this function is: =MID(Text,Start_num,Num_chars) Text – It is the piece of data you want to change. This can be a cell reference indicating where the data is stored. Start_num – it specifies the starting character from the left of the data to be kept. Num_chars – it specifies the number of characters to the right of the Start_num to be retained. For example, if you have the X and Y Coordinates in the same cell separated by a slash (/), then you can extract only the width from the data. That is, if the data is $549.5091# in the cell C3, then to get only 549.5091 in the next cell you can use the formula H2 [=MID(C3,2,8)]. 

148.png