=RIGHT( A2, LEN(A2) - FIND(",", A2) )ĭiscovering Data Using the LOOKUP Function This value will be used by RIGHT to perform the extraction of the characters determined by LEN & FIND. The result of this FIND can be subtracted from the length of the entire text string to yield the number of characters to extract. This will give us a count of all characters up to and including the comma. We can use FIND to locate the comma as before. The key is to extract all characters after the comma. The syntax and use are the same as the FIND function.īecause the descriptions are on the left end of the text in the Part Identity column, we will use the RIGHT function to extract a set number of characters from the right side of the text in Column A. If you need to locate a character or string of text in a case-insensitive fashion, use the SEARCH function. PRO TIP: The FIND function is case-sensitive. We must deduct one from the value returned by FIND to not include the comma in the results. We can then use this value to tell the LEFT function how many characters to extract from the left of cell A2. To separate the part numbers, we’ll start a new table ( in Column D) and use the FIND function to locate the character position of the comma. These two elements are together in a single cell separated by a comma. We need to separate the Part Number from the Description. We have been given a small table of Part Identities and quantities. The LEFT function extracts a set number of characters from a text string starting from the left side of the text. Subtract the result from Step 4 from the result in Step 2.Using the LEN function, count the number of remaining characters after the dots have been removed.Two continuous double quotes mean “empty text” to Excel. This will be done using the SUBSTITUTE function using two consecutive double quotes to symbolize nothing. In cell A2, count the number of characters in the cell.Insert a new column ( in this example, Column A).The logic for this substitution is as follows: An item with no periods is considered “Level 0” while an item with two periods is considered “Level 2”. If we were given a file where the hierarchy was displayed more like WBS ( Work Breakdown Structure) codes, but we need the numbers as we’ve seen in earlier examples, we can write a formula to produce these numbers.Įach period starts a new level. The LEN function will return the number of characters in a text string while the SUBSTITUTE function will replace a character or string of characters with another character or string of characters. The REPT function multiplies the “ 3” by the BOM Level value then concatenates the part number to the end. We begin by inserting a new Column C and writing the following formula in cell C1. We’ll use the REPT function to add three spaces before the part number if it is a BOM Level “ 1”, six spaces if it is a BOM Level “ 2”, and nine spaces if it is a BOM Level “ 3”. This is also a great way to achieve this look because we can easily increase or decrease the indentation by adjusting the number of spaces. We can’t feed the REPT function an indentation instruction, but we can use three spaces to simulate an indentation. This will take a lot of time, energy, and a high likelihood of inducing typographic errors. To achieve this look manually, we’d have to copy/paste the part numbers into a new column, then add a series of indentations before each part number to achieve the desired indentation level. The indents will allow us to visualize our hierarchy more clearly. The REPT function allows you to repeat a set of characters as many times as you want.įor our purposes, we want to use REPT to get indented part numbers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |