+ Reply to Thread
Results 1 to 4 of 4

Move content within parentheses to other column

  1. #1
    Registered User
    Join Date
    06-25-2015
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    5

    Question Move content within parentheses to other column

    Hi,

    I am pretty new to excel, and could need your expertise.

    I have around 2000 rows, where some of the columns contain "double information". This makes it hard for my pivot table to handle the data..

    Some columns contains two numers e.g. "0(0)" or "2.71828(1828.45)" or "-40(40)".. You get the point, two numbers, one within parentheses, in the same cell.

    I would like to copy the number within the parentheses into another column, and remove it (including the parentheses) from the original cell.

    Example:
    Cell A1: "-17(89)" --> Cell A1: "-17" and Cell B1: "89"

    Thanks in advance!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Move content within parentheses to other column

    Use this formula in B1 and copy down

    =TRIM(MID(SUBSTITUTE("("&SUBSTITUTE($A1,")","("),"(",REPT(" ",25)),25*2,25))

    Row\Col
    A
    B
    1
    2.71828(1828.45) 1828.45
    2
    -17*(89) 89
    3
    -40(40) 40
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Move content within parentheses to other column

    1.Select the cell or column that contains the text you want to split
    2.Click Data >Text to Columns
    3.This starts the Convert Text to Columns Wizard. Click Delimited > Next
    4.Check Other box and write (
    5.Click Next and Finish

    It will be 17 89) then press CTRL+H (Displays the Find and Replace dialog box)

    Write Find Box (
    Replace Box : (empty) then click change parenthesis character

  4. #4
    Registered User
    Join Date
    06-25-2015
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    5

    Re: Move content within parentheses to other column

    This worked, thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Executing methods - parentheses or no parentheses?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-23-2015, 07:57 PM
  2. [SOLVED] Move the content of specific groups of cells in column C to the first cell in that group
    By aviatecar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2014, 12:57 PM
  3. [SOLVED] Extracting all text from parentheses (including parentheses)
    By j4ke101 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-10-2013, 08:03 AM
  4. Replies: 6
    Last Post: 01-13-2011, 03:00 AM
  5. Replies: 2
    Last Post: 12-19-2006, 10:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1