Question

Locked

How to cut an paste values within brackets in Excel

By 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

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

there are formulas for text too

by databaseben In reply to How to cut an paste value ...

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)

Collapse -

Reponse To Answer

by tanoyroy In reply to there are formulas for te ...

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

Collapse -

use a combination of MID() and FIND() functions

by sgarger In reply to How to cut an paste value ...

cell in B1's formula should read:

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

Collapse -

Reponse To Answer

by tanoyroy In reply to use a combination of MID( ...

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

Collapse -

Adaption of the above to cope with numbers of any string length.

by richardhx In reply to How to cut an paste value ...

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.

Collapse -

Reponse To Answer

by tanoyroy In reply to Adaption of the above to ...

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

Collapse -

How to cut an paste values within brackets in Excel

by neerajmohta In reply to How to cut an paste value ...

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

Collapse -

if you want to add a new variable to your question

by databaseben In reply to How to cut an paste value ...

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.

Collapse -

Reponse To Answer

by tanoyroy In reply to if you want to add a new ...

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

Back to Software Forum
10 total posts (Page 1 of 1)  

Software Forums