» 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:
- Financial Peace: Revisited
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Microsoft Word Version 2002 Step By Step (With CD-ROM)
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- The Intelligent Investor: The Definitive Book On Value Investing, Revised Edition
- Excel 2002 For Dummies®
Does not work in case cell with decimles
VAIBHAV
It does not work in case cell with decimles. Is there ant formuale to resolve this issue?????
Seperate row functions
Michael
I cannot get my excel 2007 report to stop adding all of the rows into individual row functions. How do I get it to stop?!?
