Remove Numbers & Brackets In Parentheses

Oct 11, 2007

I tried to use the find and replace function to delete different numbers from different cells (all in one column) but the problem is that I want to delete different kind of numbers.

Let me give you an example:

Starting point:

Cell A1: Bookrunner: Barclays Capital(368.726m)
Cell A2: Bookrunner: DBS(368m)
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)(367.163m)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)(367.163m)

I want to reach here:

Cell A1: Bookrunner: Barclays
Cell A2: Bookrunner: DBS
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)

(I don’t want the information in “()” like “(Singapore)” to be deleted! Only the “()” which contains numbers)

I used the “find and replace” function to delete ever single number, but I think it’s a little unprofessional to do so!

View 8 Replies


Remove Parenthesis (Brackets) From Numbers

Feb 3, 2009

I have a spread sheet with 2000 fax numbers in the format:
(555) 123-1234

I am going through and reformatting them to read:

555123-1234 or 5551231234

Is there cell format code or some way to do this quickly rather than going through each cell and deleting the spaces and ()?

View 9 Replies View Related

Remove All Data Within (brackets)

Sep 2, 2009

I have a list of Surnames in Column D, e.g. ANDERSON, BROWN, COOPER, but for some reason, who ever input the data, decided to include the salutation after that Surname, in brackets, where it was available, e.g. ADAMS (Miss), BUTTON (Mr & Mrs), COX (Dr).

So I'm trtying to remove everything within the brackets (if there is anything) and place it in Column C. I will then Replace the brackets and Trim the contents in Column D. Here is what I want the code to do

View 2 Replies View Related

Remove Text With Brackets & The Parenthesis

Oct 23, 2007

I have a spreadsheet with 900 rows. All of the rows hold different information. The first column has contents such as this:

This is the text (please remove me)
This is some more text (I need to be removed too)

I want to remove from every cell, everything in the brackets - so everything from the first open brackets to the end of the cell contents). Every cell is a different length and the information within the brackets is different.

View 5 Replies View Related

Remove Text Inside Parenthesis & The 2 Brackets

Dec 15, 2007

I am importing data into excel from another application. I place the data on a sheet and then use macros to re-arrage the data to another sheet. One column of cells gets populated with strings of the following format <text1>(<text2>)
I would like to extract <text2> and place it in a cell on another sheet. the length of text1 and text2 varies.

View 4 Replies View Related

Formatting Numbers To Have Negative In Brackets?

Apr 2, 2014

I am currently using the following format to display numbers in my excel.

_(* #,###,###_);_(* (#,###,###);_(* "-"_);@

The brackets and underscores are used so that the positive and negative numbers align with overhanging brackets.

I want to modify the format such that it is able to display decimals where ever applicable.

For example

1,000 display as 1,000
0 display as a dash "-"
1.265 display as 1.265
-0.51 display as (0.51)

I tried changing it to:

_(* #,###,###.###_);_(* (#,###,###.###);_(* "-"_);@

However it added a "." to all positive and negative numbers regardless of whether there were decimals after it.


10 displayed as 10.
-30 displayed as (30.)

In otherwords - I am trying to find the "general" format and modify it to include brackets for negative number, and also modify it so that the positive numbers aligning with the negative numbers with the ) over hanging.

View 4 Replies View Related

Applying Conditional Formatting To Numbers In Brackets

Jan 28, 2014

I have some data referring to % that comes in the format of 1.1% for a positive number and (1.1)% for a negative number

How can i get the 1.1% to display green and the (1.1)% to display red?

View 1 Replies View Related

Formula To Ignore The Brackets And The Figures In The Brackets

Feb 27, 2009

I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.

I have this formula:

But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?

View 4 Replies View Related

Excel 2011 :: Removing Brackets And Spaces From Phone Numbers From Within Cell (formatting)

Feb 21, 2012

I have a spreadsheet with Mobile phone numbers in the following format:

+44(0)77 7296 5210

The spreadsheet has 2500 of these phone numbers.

Is there a way to remove the brackets and the +44 to leave:


I am using Microsoft Office for Mac 2011

View 5 Replies View Related

Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers

Aug 8, 2009

I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).

Option Explicit

Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"

View 5 Replies View Related

Add Parentheses (indicate By (32))

Nov 9, 2008

I work in school and and I have to enter test marks of students in excel 2003.

Here is my question:
If student's mark is below 40(e.g. 32), I want to indicate it by (32).

View 4 Replies View Related

What Does (3) And (1) Mean In Parentheses

Feb 5, 2014

What does the (3) and (1) mean in the parentheses?

The (3) is sloppy coding instead of using the enumeration (xlUp), which you could actually read. The (1) means the first element of the range, which is wholly redundant; it already refers to a single-cell range.

View 1 Replies View Related

Extract Data Between Parentheses?

Apr 2, 2013

I have a long list beginning either with 4 numbers or 4 numbers followed by parentheses and a single number between them. I have this formula to extract the data between parentheses:


What can I do to eliminate a "#VALUE!" result for cells that do not contain parentheses?

View 9 Replies View Related

Missing A Left Or Right Parentheses

Dec 10, 2009

I am using excel 2007:

I have created the following formula (with some help from this forum), but I am getting an error message saying I am missing a left or right parentheses.

View 9 Replies View Related

Extracting Data Within Parentheses

Sep 15, 2006

I need to come up with a formula that will pull the data located within parentheses. Here is an example below:

LINDA DALEY (503094)
STEPHAN DANIEL (100360949)

View 7 Replies View Related

Copy Text Within Parentheses From One Cell To Another

Oct 20, 2012

I want to do something similar but it is looking for values between parentheses "()" and won't work with the VB suggested. I've seen some other formulas that do find the content within the parentheses but returns #VALUE! if there is not a parentheses in the field. I'd like it to copy the original value of A2 into B2 for those instances. PROBLEM: Database with First Names in A2 where some have "nicknames" entered within () and I need a clean column of values to use. "IF A2 contains () then copy (contents) into B2, else copy A2" is how I'd write in english... not sure how to do correctly in code/vb.

Sub PartialText()
Dim MyValue$
Dim ChangedValue$


View 5 Replies View Related

Removing Parentheses From Text Cell

Jan 3, 2013

An externally generated CSV file gives us one column which shows a location, year and then individual names in brackets, like so:

London 2013 (Firstname Lastname)
Delhi 2012 (Firstname Lastname)

I'm wondering if it is possible to separate out the text before the parentheses, so that I can show "London 2012" (for example) in another cell. The number of characters changes from row to row, so using the LEFT and RIGHT functions doesn't quite do the job.

View 4 Replies View Related

Parentheses For Negative Percent Results -

Jul 15, 2002

I have been able to format single cells to display negative percents (Budget to Actual hours), but I cannot copy the formatting to cells with positive percents without eliminating the format style I want. [I need to display, with the parenthesis, (13.6%)for negative results, but say, 18.6% for positive results.] When I copy the correctly formatted cell (13.6%) to another cell with a positive result, it sets the display to general formating.

As I have over 25 rows of data to compare against 62 projects and 12 programs, with each value potentially changing from one analysis to the other, I am looking for a method to automatically change the "look" of the results. I have looked at conditional formatting, but have had no indication this will do what I am looking for.

View 3 Replies View Related

Goal Seek VBA Syntax - With / Without Parentheses

Nov 25, 2008

I've been playing around with GoalSeek in VBA because of this thread and found something about the syntax that I don't understand.

If I want to find out whether GoalSeek worked, I can use

Ok = Range("B1").GoalSeek(Goal:=10, ChangingCell:=Range("A1"))
Debug.Print Ok
This requires parentheses around the GoalSeek arguments.

If I just want to do the GoalSeek, then I need

Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")
with no parentheses. Why? The Help says that GoalSeek returns True if it works, and the proforma syntax includes parentheses, but then the only example uses the syntax without parentheses.

View 9 Replies View Related

Nesting IF Statements: Use Of Isblank Or Lack/placement Of Parentheses

Jun 2, 2009

I'm trying to nest if statements that also include "and" and "isblank" factors. The following formula isn't working, and I'm not sure if it's because of my use of isblank or lack/placement of parentheses.

View 3 Replies View Related

Formula For Removing Text Within Multiple Sets Of Parentheses?

May 22, 2014

Any formula for removing text within multiple sets of parentheses?

I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.

and have it read: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.

I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.

NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.

View 9 Replies View Related

How To Remove First 2 Digits If They Are Certain Numbers

Jan 29, 2014

I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.

I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.

View 12 Replies View Related

Remove All Characters But The Numbers

Sep 10, 2009

there is a none VBA way to remove everything from a cell but the numbers.

Example: A, DAVID (002081) becomes 002081

The cell the formula will go in is F4 and the cell its looking at is K4.

View 9 Replies View Related

Remove Spaces From Numbers

Jul 8, 2007

I wrote numbers in column b cells in disordered way :

.. 1
.... 1
.. 1

I would like to make them to be aligned in the left side by a code.

View 9 Replies View Related

Remove Plus Signs From Numbers

Mar 26, 2008

I have a list of numbers in Column A that I always copy into another sheet. The problem I have is that some of these numbers end in "+" and I can't use them when it has that. I have to go through and one by one take out the plus. I was wondering if it was possible to copy only the numerical values to a new column?


Original What Im Looking For

4 4
56 56
8+ 8
5 5
90 90
2 2
4+ 4
45 45
1 1
67 67

Auto Merged Post Until 24 Hrs Passes;Actually I just figured it out. I did a text to columns with a + sign as the separator and it worked.

View 4 Replies View Related

Editing Formula Shows Text All Red, Parentheses Color-coded

Oct 20, 2007

But the formula seems to be working properly.

I've got ALOT of parentheses, 5 nested IFs, 5 ANDs, 4 ORs. The ANDs and ORs are within the IFs.

While editing the formula, I can, at times, achieve the condition where the cell references become color-coded. Or at least some of them do. But, when I get to this point, I'm usually at a spot where I can see the formula isn't right.

I'm confused. What does it mean when my text is all red when I go to edit a formula? Did I hit some limit? Again, I don't get error messages and the formula seems to work properly.

View 9 Replies View Related

Remove Numbers From Alphanumeric Cells

Sep 17, 2008

I have about 10000 cells of varying length in text and numbers but they all end with numbers on the ends of them. Using text to columns doesn't work because they're all different lengths and it cuts them off in the wrong place.

Is there a formula I can use that will return only the text from these cells and remove the numbers. It would definitely save me time from individually deleting the numbers at the end.

View 11 Replies View Related

Cell With Name, But Want To Remove Everything Else, Numbers, Decimals Etc.

Feb 25, 2009

I'm trying to clean up a very large list of last names. Only one individual cell, but that cell includes numbers, decimal points, and spaces inbetween the numbers. All I want left in the cell is the last name. I have just under 100,000 to do! How would I go about this? Using Excel 2007.....

View 2 Replies View Related

Format Cell To Remove Numbers

Nov 26, 2009

I m looking to format a cell to remove numbers that i don't need. For example, at work we can swipe a card and the card number comes up like so.


is there a way to format the cell to remove everything but the 0370904

View 5 Replies View Related

Remove Numbers From Alphanumeric String?

Jan 11, 2013

I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.

01 Agricultural Production Crops
011 Cash Grains
0111 Wheat
0112 Rice
0115 Corn
0116 Soybeans
0119 Cash grains, nec
011901 Pea and bean farms (legumes)
01190101 Bean (dry field and seed) farm
01190102 Cowpea farm
01190103 Lentil farm
01190104 Mustard seed farm

View 3 Replies View Related

Copyrights 2005-15, All rights reserved