+ Reply to Thread
Results 1 to 3 of 3

Need formula to split column

  1. 04-20-2015, 12:32 AM #1
    SM3T
    SM3T is offline
    Registered User
    Join Date
    04-13-2015
    Location
    QUEENSLAND, AUSTRALIA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Exclamation Need formula to split column

    I have a column with mobile phone numbers as well as landlines:

    0403920493
    02 9345 9483
    93545493

    I need a formula to split up the mobile numbers and the landlines. It could either work by:
    - First splitting off the numbers that begin with '02', then apply another formula to split the ones that begin with '9' - I will then merge to the landlines columns that I just created
    - Split off any numbers that begin with either '02' or '9'

    What I don't want is for the formula to rather split off any numbers that begin with '04'.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 04-20-2015, 12:48 AM #2
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Need formula to split column

    not quite sure what your after

    something like attached?

    if not give a longer list of numbers and list of what your expect to see
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 04-20-2015, 12:49 AM #3
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Need formula to split column

    Assuming your phone numbers are in col. A in the range A2:A1000, then try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

    In B2 (for Landline numbers)
    Please Login or Register  to view this content.
    Confirm with Ctrl+Shift+Enter and then copy down.

    In C2 (for Mobile numbers
    Please Login or Register  to view this content.
    Confirm with Ctrl+Shift+Enter and then copy down.

    Is this what you are trying to achieve?

    ********************************************
    Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Split Data From Single Column Set Into Multiple Column Sets?
    By slavrenz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2014, 04:33 AM
  2. How to split one column (Addresses, City, State, Zipcode, attn) into multiple column?
    By kittya in forum Excel General
    Replies: 3
    Last Post: 08-19-2014, 10:24 AM
  3. [SOLVED] split comma separated names in a column to a new column
    By letsxcel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-16-2012, 10:45 AM
  4. [SOLVED] Split One Column at Delimiter While Copying Contents of Second Column to Populate New Rows
    By medailSacrosanct in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-01-2012, 11:37 AM
  5. How do I split data in a column (formula)?
    By dan in forum Excel General
    Replies: 7
    Last Post: 07-05-2005, 08:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1