How to Split Number and Text in Excel? Microsoft Excel, the trusted workhorse of data analysis and management, is a tool with immense capabilities waiting to be harnessed. In the world of data, we often encounter information that’s neatly entwined with both numbers and text. Imagine dealing with complex addresses, product IDs, or financial data where numbers and text dance together, making it a formidable challenge to work with.
But fear not, as we embark on a journey through the fascinating landscape of Excel. In this article, we will learn about five ingenious methods that will empower you to effortlessly split numbers and text in Excel.
5 Methods to Split Numbers and Text in Excel
1. Using Text to Columns
By using the Text to Columns wizard, you can easily divide the contents of a single cell into many columns.
Step 1: Select the cell or range of cells you want to split.
Step 2: Navigate to the “Data” tab.
Step 3: Click on “Text to Columns.”
Step 4: Choose the “Delimited” option.
Step 5: Select the delimiter that separates the numbers and text (e.g., space or a specific character).
Step 6: Click “Finish.”
Excel will split the contents into separate columns based on your chosen delimiter.
2. Using Power Query
Power Query, a dynamic tool of Excel, makes the task easy by providing a user-friendly interface for data transformation. It allows you to effortlessly separate mixed content into neatly organized columns with just a few clicks.
Step 1: Select your data range.
Step 2: Move to the “Data” tab and click “Get Data.”
Step 3: In the Power Query Editor, use the “Select Data Source” feature and specify the delimiter.
Step 4: Select New Source to import the data
Step 5: In the Power Query Editor, use the “Split Column” feature and specify the delimiter.
Step 6: Click “OK” to split the data.
3. Using Flash Fill
This is likely to be the simplest, most adaptable, and quickest way to extract numerical data from text. With Flash Fill, you may populate a column with values based on one or two samples. The great thing about this function is that it does not require you to navigate to any menus, adjust any settings, or recall any formulas.
Step 1: In a new column next to your mixed data, type the desired format for the split (e.g., “Text-Number”). Start with the first cell.
Step 2: Activate Flash Fill: After typing the desired format, press “Ctrl + E” (Windows) or “Command + E” (Mac). Excel will automatically recognize the pattern and split the data in the adjacent column based on your input.
Step 3: Review and Adjust: Excel will provide a preview of the split data. If it is correct, press “Enter” to accept it. If not, you can manually edit the suggestions or continue pressing “Ctrl + E” (or “Command + E”) to cycle through more suggestions until you are satisfied with the split.
4. Text Functions – FIND and SEARCH
FIND and SEARCH are Excel text functions used to locate a specific separator (e.g., space or character) within a cell containing both numbers and text. You can use these functions to determine the position of the separator and then extract the numbers and text accordingly.
Step 1: In a new column, use the formula =LEFT(A1, FIND(“delimiter”, A1) – 1) to extract the text.
Step 2: In another column, use the formula =MID(A1, FIND(“delimiter”, A1), LEN(A1)) to extract the numbers.
*Replace “delimiter” with the actual separator in your data.
5. Using a Custom VBA macro
If your data splitting needs are complex and repetitive, consider using a VBA (Visual Basic for Applications) macro to automate the process. This method is suitable for advanced users who are comfortable with programming in Excel.
Step 1: Open the VBA Editor.
Step 2: In Excel, press “Alt + F11” to open the VBA Editor.
Step 3: In the VBA Editor, go to “Insert” in the menu and select “Module” to add a new module.
Step 4: In the module, write the VBA code to split numbers and text. Here’s a sample macro that splits data in column A and places text in column B and numbers in column C:
Sub Split_Numbers_And_Text() Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow Dim CellText As String CellText = Cells(i, 1).Value Dim strText As String Dim strNumber As String For j = 1 To Len(CellText) If IsNumeric(Mid(CellText, j, 1)) Then strNumber = strNumber & Mid(CellText, j, 1) Else strText = strText & Mid(CellText, j, 1) End If Next j Cells(i, 2).Value = strText Cells(i, 3).Value = strNumber Next i End Sub
Step 5: Close the VBA Editor and return to your Excel workbook.
Step 6: Press “Alt + F8” to open the “Macro” dialog.
Step 7: Select the “Split_Numbers_And_Text” macro and click “Run.”
This custom macro will split numbers and text in your selected range (column A in this example) and place them in separate columns (columns B and C).
Note: – Please remember to save your Excel file with macros as a .xlsm format to retain the macro code.
Conclusion
These methods provide a range of options to split numbers and text in Excel, from basic built-in tools to more advanced techniques. The choice of method depends on the complexity of your data and your proficiency with Excel. Whether you are a beginner or an experienced user, Excel offers solutions for efficiently managing your data and making it more accessible for analysis and reporting.
