In this example, since we are assuming that the number will always appear second in the original text, it works well because MIN forces only the smallest, or first occurrence, of a number to be returned. Unfortunately, FIND doesn't return zero when a value isn't found, so this is just a clever way to avoid errors that could occur when a number isn't found. This part of the formula concatenates every possible number 0-9 with the original text in B5. You might be wondering about the odd construction for within_text in the find function: B5&"0123456789" In essence, the FIND function gets all number positions, and MIN gives us the first number position: notice that 7 is the smallest value in the array, which corresponds to the position of the number 3 in original text. Next the MIN function returns the smallest value in the list, which corresponds to the position in of the first number that appears in the original text. From the original text, which appears in one cell, you want to split the text and numbers into separate cells, like this: OriginalĪs stated above, the key in this case is to locate the starting position of the number, which you can do with a formula like this: =MIN(FIND(Įach number in this array represents the position of an item in the array constant inside the original text. In this case, we are assuming that numbers and text are combined, and that the number appears after the text. Once you have the position, you can use other functions to extract what you need. The formula looks complex, but the mechanics are in fact quite simple.Īs with most formulas that split or extract text, the key is to locate the position of the thing you are looking for.
0 kommentar(er)
