![]() Keeping in mind that a robust formula should be kept simple, I will share the code of the user-defined function (UDF) to strip off any numeric character. Find the position of the number in the string. The solution works for all Excel versions Here are three steps you can follow to separate numbers from text in Excel with formulas: 1. Custom function to remove numbers from text This final array is passed over to TEXTJOIN, so it concatenates the text characters and outputs the result. ![]() In B2, this part of the formula looks as follows: You use either ROW(INDIRECT("1:"&LEN(string))) or SEQUENCE(LEN(string)) to create a sequence a numbers corresponding to the total number of characters in the source string, and then feed those sequential numbers to the MID function as the starting numbers. Method 1: Using the Flash Fill feature to separate texts and numbers in Excel. ![]() To better understand the logic, let's start investigating the formula from the inside: =TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Īs the result, all text characters are removed from a cell and numbers are kept: after the £ symbol to get commas to separate groups, and to use a period for the decimal point. In dynamic array Excel, it works as a normal formula completed with the Enter key. Just type the format to use in quotes -> insert the £ symbol by holding down Alt and pressing 0163 on the numeric keypad -> type. In Excel 2019, it must be entered as an array formula with Ctrl + Shift + Enter. TEXTJOIN("", TRUE, IFERROR(MID( cell, SEQUENCE(LEN( cell)), 1) *1, ""))Īt first sight, the formulas may look a bit intimidating, but they do work :)įor example, to remove text from numbers in A2, enter one of the below formulas in B2, and then copy it down to as many cells as needed.
0 Comments
Leave a Reply. |