» Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Problem: 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:
- Dictionary of Finance and Investment Terms
- Microsoft Access 2002 for Dummies
- Fish! A Remarkable Way to Boost Morale and Improve Results
- Business Analysis with Microsoft Excel (2nd Edition)
- Microsoft Access Version 2002 Step by Step
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
No comments have been submitted.
