+ Reply to Thread
Results 1 to 3 of 3

Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters

  1. #1
    VAIBHAV
    Guest

    Does not work in case cell with decimles

    It does not work in case cell with decimles. Is there ant formuale to resolve this issue?????

  2. #2
    Michael
    Guest

    Seperate row functions

    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?!?

  3. #3
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1