![]() ![]() ![]() Separate Text and Numbers Using VBA (Custom Function) Once you have it, you can again use the LEFT function or the MID function to separate the numbers and text. You can still use the same logic with one minor change – instead of finding the minimum value that gives us the position of the 1st digit in the cell, you need to use the MAX function To find the position of the last digit in this cell. To extract the numbers, where we know the starting position of the first digit, use the MID function to extract everything starting from that position.Īnd what if the situation is reversed – where we have the numbers first and the text later and we want to separate the numbers and text? Similarly, you can use the same formula with a minor tweak to extract all the numbers after the text. Now that we know where the numerical values start, I have used the LEFT function to extract everything before this position (which would be all the text in the cell). ![]() Since each number in the array represents the position of that corresponding number, the minimum value tells us where the numerical value starts in the cell. The MIN function then goes through the above result and gives us the minimum value from the results. The result of it looks like as shown below: This FIND formula is then wrapped inside the IFERROR function, which removes all the value errors but leaves the numbers. For 1, it returns 4 as that is the position of the first occurrence of 1 in cell A2.For 0, it returns #VALUE! as it cannot find this digit in cell A2.Let me first explain the formula that we use to separate the text part on the left. ![]()
0 Comments
Leave a Reply. |