Questions

How to cut an paste values within brackets in Excel

+
1 Votes
Locked

How to cut an paste values within brackets in Excel

tanoyroy
Hello Experts

A1: Bahamas(21)

My question, how can I cut and paste the value with brackets in B1??

which shud finally look as follows:
A1: Bahamas
B1: 21 or (21)

Thanks in advance for your help.
cheers, TR
  • +
    0 Votes
    databaseben

    there is a formula that allows you to count any number of digits from the right hand side. i don't recall the formula but if i remember it looks something like this:

    right ("cell ref", 4)

    the above captures the four digits from the right hand side from a particular cell, which will then produce the value:

    (21)

    +
    0 Votes
    tanoyroy

    Thanks for your response.
    The problem with your solution is that the value contained within brackets can vary in size e.g. less of more than 4..
    rgds

    +
    0 Votes
    sgarger

    cell in B1's formula should read:

    =MID(A1, FIND("(",A1)+1, 2)

    +
    0 Votes
    tanoyroy

    Thanks for your prompt response.
    I did try the above formula and I am getting a error msg. "Invalid", in the ???Function Arguments fx??? window, under the row ???Text??? ?
    BTW I am using MSExcel version 2010.
    I replaced the comma's with semicolumn's, it worked but the problem is that the cell displays the formula instead of displaying the value of the formula e.g. 21 ?
    What am I doing wrong ?
    Kind rgds

    +
    0 Votes
    richardhx

    Using the same Idea, you can do a calculation based on the FIND function, to set the length of the string to "MID".

    =MID(A1, FIND("(",A1)+1, (FIND(")",A1)-(FIND("(",A1))-1))

    Works for the 2011 version (Mac OSX),
    tanoyroy: Most likely that you missed out the "=" at the start of the formula. Using comma is the correct syntax for Excel formulas. ":" is used for Ranges.

    Bear in mind that the result from doing this as is results in a string and not a number. So Doing an SUM(B1,B2,...) results in 0.

    Adding Value() to the formula allows a Sum, or statistical calculations to be carried out on columns containing the formula.
    =Value(MID(A1, FIND("(",A1)+1, (FIND(")",A1)-(FIND("(",A1))-1)))
    Then to sum a column simply use SUM(B1:Bn). Hope this helps.

    +
    0 Votes
    tanoyroy

    Thanks for your reply.
    Yes I did use the correct syntax "=" at the start of the formula. I tried your for formula MID as mentioned above but I still get the error message as follows:
    The formula you typed contains an error.

    Only the semicolons (;)instead of comma (,) works but the field dispays only the formula and not the value of the formula ??

    regds. Roy

    +
    0 Votes
    neerajmohta

    If you have the Bahamas(21) in A1, you could use the following formulas to have (21) showing up on A2 and Bahamas showing up on A3

    =MID(A1,SEARCH("(",A1,1),SEARCH(")",A1,1)) -----> This you need to put in A2
    =LEFT(A1,SEARCH("(",A1,1)-1) ------>This you need to put in A3

    +
    0 Votes
    databaseben

    Thanks for your response.
    The problem with your solution is that the value contained within brackets can vary in size e.g. less of more than 4..
    rgds
    tanoyroy@... 6 hrs ago


    Well, it doesn't mean that my response wasn't the exact answer to your exact question.

    If you want to add another variable to your question, then build another formula to locate a constant, like the "space" or the "("

    the formula will then produce a unique value which tells you where that unique character / constant is located in the string, which will be the number of characters into the string.

    then reference that unique value in another formula, such as the one i initially offered where the value 4 can now replaced with the cell referencing the unique value above.

    afterwards, you can further clean up the extracted characters with another formula that can replace the "(" and ")" with "", to remove the brackets.

    fyi - you can separate all the sub formulas in various cells to ensure that each produces a simplistic and desired value.

    later, after you are assured that each sub formula works on its own, you can combine them into a single equation inside a single cell.

    as you will find by trial and error, excel has a number of formulas to manipulate text and values. you just have to try them all out on an experimental worksheet to see their potential.

    +
    0 Votes
    tanoyroy

    Once again thanks for your response. Yes I agree you solution was exactly tailored to my question. I was able to solve the problem by using the following formula in the cell B1:
    =MID(A1;FIND("(";A1)-1;9)
    I used semicolons (;) instead of commas

    rgds

  • +
    0 Votes
    databaseben

    there is a formula that allows you to count any number of digits from the right hand side. i don't recall the formula but if i remember it looks something like this:

    right ("cell ref", 4)

    the above captures the four digits from the right hand side from a particular cell, which will then produce the value:

    (21)

    +
    0 Votes
    tanoyroy

    Thanks for your response.
    The problem with your solution is that the value contained within brackets can vary in size e.g. less of more than 4..
    rgds

    +
    0 Votes
    sgarger

    cell in B1's formula should read:

    =MID(A1, FIND("(",A1)+1, 2)

    +
    0 Votes
    tanoyroy

    Thanks for your prompt response.
    I did try the above formula and I am getting a error msg. "Invalid", in the ???Function Arguments fx??? window, under the row ???Text??? ?
    BTW I am using MSExcel version 2010.
    I replaced the comma's with semicolumn's, it worked but the problem is that the cell displays the formula instead of displaying the value of the formula e.g. 21 ?
    What am I doing wrong ?
    Kind rgds

    +
    0 Votes
    richardhx

    Using the same Idea, you can do a calculation based on the FIND function, to set the length of the string to "MID".

    =MID(A1, FIND("(",A1)+1, (FIND(")",A1)-(FIND("(",A1))-1))

    Works for the 2011 version (Mac OSX),
    tanoyroy: Most likely that you missed out the "=" at the start of the formula. Using comma is the correct syntax for Excel formulas. ":" is used for Ranges.

    Bear in mind that the result from doing this as is results in a string and not a number. So Doing an SUM(B1,B2,...) results in 0.

    Adding Value() to the formula allows a Sum, or statistical calculations to be carried out on columns containing the formula.
    =Value(MID(A1, FIND("(",A1)+1, (FIND(")",A1)-(FIND("(",A1))-1)))
    Then to sum a column simply use SUM(B1:Bn). Hope this helps.

    +
    0 Votes
    tanoyroy

    Thanks for your reply.
    Yes I did use the correct syntax "=" at the start of the formula. I tried your for formula MID as mentioned above but I still get the error message as follows:
    The formula you typed contains an error.

    Only the semicolons (;)instead of comma (,) works but the field dispays only the formula and not the value of the formula ??

    regds. Roy

    +
    0 Votes
    neerajmohta

    If you have the Bahamas(21) in A1, you could use the following formulas to have (21) showing up on A2 and Bahamas showing up on A3

    =MID(A1,SEARCH("(",A1,1),SEARCH(")",A1,1)) -----> This you need to put in A2
    =LEFT(A1,SEARCH("(",A1,1)-1) ------>This you need to put in A3

    +
    0 Votes
    databaseben

    Thanks for your response.
    The problem with your solution is that the value contained within brackets can vary in size e.g. less of more than 4..
    rgds
    tanoyroy@... 6 hrs ago


    Well, it doesn't mean that my response wasn't the exact answer to your exact question.

    If you want to add another variable to your question, then build another formula to locate a constant, like the "space" or the "("

    the formula will then produce a unique value which tells you where that unique character / constant is located in the string, which will be the number of characters into the string.

    then reference that unique value in another formula, such as the one i initially offered where the value 4 can now replaced with the cell referencing the unique value above.

    afterwards, you can further clean up the extracted characters with another formula that can replace the "(" and ")" with "", to remove the brackets.

    fyi - you can separate all the sub formulas in various cells to ensure that each produces a simplistic and desired value.

    later, after you are assured that each sub formula works on its own, you can combine them into a single equation inside a single cell.

    as you will find by trial and error, excel has a number of formulas to manipulate text and values. you just have to try them all out on an experimental worksheet to see their potential.

    +
    0 Votes
    tanoyroy

    Once again thanks for your response. Yes I agree you solution was exactly tailored to my question. I was able to solve the problem by using the following formula in the cell B1:
    =MID(A1;FIND("(";A1)-1;9)
    I used semicolons (;) instead of commas

    rgds