+ Reply to Thread
Results 1 to 4 of 4

Formula to insert parentheses

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    MN
    MS-Off Ver
    2010, 2013
    Posts
    26

    Formula to insert parentheses

    (I have around 600 species names)
    I need to be able to insert parentheses around the “common names” of different species. Some species will have different number of names.
    Right now, my list looks like this:
    Asclepias amplexicaulis Sand Milkweed
    Agastache scrophulariaefolia Purple Giant Hyssop
    Aster ciliolatus Lindley's Heart-leaved Aster
    Aster novae-angliae New England Aster

    I need it to look like this:
    Asclepias amplexicaulis (Sand Milkweed)
    Agastache scrophulariaefolia (Purple Giant Hyssop)
    Aster ciliolatus (Lindley's Heart-leaved Aster)
    Aster novae-angliae (New England Aster)

    THANKS!!!

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Formula to insert parentheses

    Assuming the latina name is always 2 words separated by a space, you could try this formula:
    Your original text is in cell A1 and you put this formula in cell B1
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    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: Formula to insert parentheses

    Try this array formula in B1 and copy down

    =LEFT(A1,MATCH(1,(CODE(MID(RIGHT(A1,LEN(A1)-1),ROW($A$1:$A$255),1))<=90)*(CODE(MID(RIGHT(A1,LEN(A1)-1),ROW($A$1:$A$255),1))>=65),0))&"("&TRIM(MID(A1,MATCH(1,(CODE(MID(RIGHT(A1,LEN(A1)-1),ROW($A$1:$A$255),1))<=90)*(CODE(MID(RIGHT(A1,LEN(A1)-1),ROW($A$1:$A$255),1))>=65),0),LEN(A1)))&")"

    Row\Col
    A
    B
    1
    Asclepias amplexicaulis Sand Milkweed Asclepias amplexicaulis (Sand Milkweed)
    2
    Agastache scrophulariaefolia Purple Giant Hyssop Agastache scrophulariaefolia (Purple Giant Hyssop)
    3
    Aster ciliolatus Lindley's Heart-leaved Aster Aster ciliolatus (Lindley's Heart-leaved Aster)
    4
    Aster novae-angliae New England Aster Aster novae-angliae (New England Aster)
    Last edited by AlKey; 10-20-2014 at 12:13 AM.
    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

  4. #4
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: Formula to insert parentheses

    Assuming the first word in parentheses will always be the 1st capitalized word in the string (excluding the very first word):

    =LEFT(A1,MIN(IFERROR(FIND(CHAR(32)&CHAR(ROW($65:$90)),A1),999)))&"("&REPLACE(A1,1,MIN(IFERROR(FIND(CHAR(32)&CHAR(ROW($65:$90)),A1),999)),)&")"

    You must confirm it with Ctrl+Shift+Enter since it's an array formula.

+ 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. Missing formula parentheses.
    By ThursRed in forum Excel General
    Replies: 6
    Last Post: 08-13-2015, 12:53 AM
  2. [SOLVED] Parentheses Placement IF/AND Formula?
    By artiststevens in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-24-2014, 12:21 AM
  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. Array formula and parentheses
    By HSU in forum Excel General
    Replies: 16
    Last Post: 12-22-2010, 01:23 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