» Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
The strings in column A can be composed of both numbers and characters; however, the numbers may only appear at the beginning of the string and the characters at the end.
We want to separate the strings into two columns, one containing only numbers and the other containing characters.
Solution:
Use the LEFT, MAX, IF, ISNUMBER, MID, INDIRECT, and ROW functions as shown in the following Array formula entered in column B:
{=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:1024")),1)),ROW(INDIRECT("1:1024")))))}
Then use the SUBSTITUTE function as shown in the following formula entered in column C:
=SUBSTITUTE(A2,B2,"")
To apply Array formula:
Select the cell, press
Book Store:
Recommended Books:
- Microsoft Excel Version 2002 Step by Step
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- How to Read A Financial Report
- Dictionary of Finance and Investment Terms
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
No comments have been submitted.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
