Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










How To Extract A Value From Between Brackets


I have a value that is bracketed and I just want the number extracted to another cell.

I've shown three part lines of the data I need to extract from.

Cd56.5,(53.5),1:34.16,(36.22),
53.0,(53.0),1:36.32,(37.36),6/1,
-2.0,(54.0),1:59.26,(36.52),9/1,

The values I need are 53.5 from line one-53.0 from line two & 54.0 from line three.

As you can see they don't always sit in the same column.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Extract Data In Brackets/Parenthesis
I am importing into Excel a list of contacts from a txt file so I can make a mailing label database. I am able to seperate out the various parts of the address just fine but here's where it gets tricky.

Below is a example of the text I am importing:
Mr Dow Jones, 600 DIXON ROAD, TORONTO, ON M9W 1J1, CANADA (1-416-6757611)

I need Excel to look at the cell containing the country name and: 1st. see if there is a phone number present (this is the numbers between the () and if there is - place the phone number in Cell F1 and place the Country in Cell E1 but strip out both the () and the phone number. If there is no phone number present (and therefore no () I need Excel to go ahead and place the country name in E1

View Replies!   View Related
How To Extract A String Within Brackets Repeatedly
I have the following information in a cell. I want to get the exchange:ticker which is inside the brackets to be returned (coma delimited). How can I do this? One thing to note here that the number of companies in the cell can vary

Banco Bilbao Vizcaya Argentaria (CATS:BBVA); Banco Santander, S.A. (CATS:SAN); Banesto Banco Espanol de Credito SA (CATS:BTO); Banco Popular Espanol SA (CATS:POP); Caja de Ahorros y Monte de Piedad de Madrid; La Caixa Group, Asset Management Arm

View Replies!   View Related
Extract Text Strings Within Brackets From Range
I have a column of data in the following format:

Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)

etc

I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.

For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.

I can do it in excel by:

(Assuming data is in cell V36)

'in colum x
=FIND("(",V36)

'in column y
=FIND(")",V36)

'in column z
=MID(V36,X36+1,Y36-(X36+2))

in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)

I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.

View Replies!   View Related
Formula To Ignore The Brackets And The Figures In The Brackets
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:
=AND(N3<=$U$1,MOD($U$1,N3)=0)

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 Replies!   View Related
Too Many Brackets At The End
=IF(AND(A1>=1,A11001,A1=10001,A1=25001,A1=1500001),(A1*0.333)+25000)))))

What is missing? (too many brackets at the end?) absolutely I've just mucked around with the formula given so have done something incorrect I think

A1 will contain Sales and I need to work out how much commission they'll get (ie, sales of between 25,000 & 150000 will get 50% plus $2500

View Replies!   View Related
Automating Brackets
I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.

So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.

Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.

View Replies!   View Related
Deleting Brackets
I have a long list of names with their locations in brackets. What formula should i use to delete the brackets and their contents only?

View Replies!   View Related
{} Brackets Disappear
I have a formula

{=IF(OR(D9=M4:S99),"m")}

For instance

This formula works

But when i click in the cell to edit the formula the {} (at the beginning and end) disapear and the formula no longer works.
If i add them back manually the formula still does not work.

How can i edit these formulas without stopping them working.

View Replies!   View Related
If Function, Sourround Value With Brackets
i have several large tables of numbers. For my purpose I have to compare each entry of the table with a certain value, no problem so far.

Now if the entry is lower than the certain value i want to have the table entry sorrounded by brackts e.g. 0.8 spould change to (0.8).

Usually i did this by right mouse -> format cell -> number -> user define -> "("0.0")" for each entry individually. Now im getting tired ;-)

Is it possible to implicate the formating of a value into an if function ?

View Replies!   View Related
Square Brackets - Vba Notation
I often see square brackets around ranges in code. eg [A1]

if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?

View Replies!   View Related
Remove All Data Within (brackets)
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 Replies!   View Related
Use Of Square Brackets In Vba 2003
My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is

compile error:
can't find project or library

What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing, here is a small part of my code

View Replies!   View Related
Formula Entered With { } Brackets
I have a formula in a spreadsheet that must be entered by selecting Command (Apple) and Return. This puts parentheses around the entire formula. Example: {=A1+B1}

I have it in a SUM IF formula: {=SUM(IF('Dollars'!$A$2:$A$2546=A116,IF('Dollars'!$B$2:$B$2546=B116,'Dollars'!$E$2:$E$2546)))}

If it's not entered this way, it will not work. You cannot simply hit return for the formula to work.

Since I did this so long ago (4 years), I cannot remember what it's called; why it has to be done that way and how to do the same thing in Windows Excel.

View Replies!   View Related
Indirect To Sheets With Brackets
i encountered a problem with using the Indirect formula. it gives #REF error when i use it to refer to a sheet with brackets in them for example i want to refer to sheet "Data 101(1)" =INDIRECT(A1&"!A1"). I'm not allowed to change the sheetnames. is there a way around this using formula or vba?

View Replies!   View Related
Remove Parenthesis (Brackets) From Numbers
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 Replies!   View Related
Calculate Number In Brackets/Parenthesis
Is it possible to extract a number in brackets specifically from a cell, then increase it by 15%, and return the result back into the brackets.

For example the cell consists the following:
Mary (78)

so the aim is to get the 78 out for calculation, then change the content of the cell with the new result.

Or is there a macros that can be created for this?

View Replies!   View Related
Remove Numbers & Brackets In Parentheses
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 Replies!   View Related
Remove Text With Brackets & The Parenthesis
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 Replies!   View Related
Using Name Ranges To Group Customers Into Different Pricing Brackets
I have a worksheet that needs customers split into different price bands

here we go

I have 13 customers all from the same group (a)
1 customer in group (b)
1 Customer in group (c)
& 80 independant customers (d) worksheet only has 9 (1,2,3,4,5,6,7,8,9)

Column a is a list
D17 is the drop down list
D21 is the starting price

C2:D5 is the pricing multiple to be applied

What I am trying to achieve is when an independant company is chosen it defaults to d for pricing.

View Replies!   View Related
Modify Array Forumula And Preserve Brackets
How would I modify a (very long) array formula, while preserving the brackets? Or re-creating them? I've tried doing it "at the cell" without luck.

View Replies!   View Related
Remove Text Inside Parenthesis & The 2 Brackets
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 Replies!   View Related
Enclose & Display Variable Within Parenthesis/Brackets
I'm trying to enclose a variable declared as string with a numeric value within parentheses. here's the code I have...

Dim strQuery As String, strPortIP As String
strQuery = Application.InputBox(Prompt:="Please enter the port or IP you want:", Title:="Query Input", Type:=2)
strPortIP = Application.InputBox(Prompt:="Is this a port or IP?",Title:= "Query Type", Type:=2)

If strPortIP = "Port" Or "port" Then strQuery = "(" & strQuery & ")"

I'm getting run-time error '13': type mismatch on the "If..." statement. I'm trying to set the variable strQuery to "(80)", for example, to do a Find within my data. Without the "()" I get ALL occurrences of "80" and that's not what I need...

View Replies!   View Related
Move Data In A Cell Within Brackets To Another Cell
Is it possible to move data within brackets and the brackets themselves from one cell to another?

Eg. Audi A3 (Including S3) all models.

To remove (Including S3) from this cell and place it into another cell?

View Replies!   View Related
Extract Date
Once a week I recieve a data file from another department and their report shows the period starting as well as ending but in text format all in one cell.

How can I extract the two dates to show the srart date in one cell and the end date in another. The example that would arrive in cell A1 is:

Reporting Period: 01/01/2009 00:00 to 07/05/2009 23:59

The hours and minutes do not matter.

View Replies!   View Related
Extract The Integer
Below is a snapshot of data in a column. I need a way to extract the number, may be a whole, may be decimal, and leave behind the text description. Result to be put into a new column:

Hernia Umbilical 553.1
Perirectal Abscess 566
Diverticulitis 577.0
553.21 Hernia Vental/Incisional
Abdominal Pain RUQ789.01
Abdominal Pain RLQ789.03
217: Benign Breast mass
541-appendicitis
Chronic 575.11 Cholecystitis
SBO560.81
786.05SOB
226/ Thyroid neoplasm

The idea here is that the integer could be integrated in anyway imaginable with the associated text. I don't need the text. The end result must display this in a new column:

553.1
566
577.0
553.21
789.01
789.03
217
541
575.11
560.81
786.05
226

View Replies!   View Related
Use Mid To Extract String
I need to extract the term between the two colons: Snowboard Jackets

This term resides in AB289-
Snow : Snowboard Jackets : Guys SoftShell

Using MID, but not able to get all the term


View Replies!   View Related
EXTRACT Text ..
I trying to extract the group, sqd, office1 and office2 from a symbol. Each is seperated by "/".

I can get the Group pretty easy but after that its been giving me trouble.

View Replies!   View Related
Extract Value With Decimal
Extract 2 digits to the right of a decimal when it ends in 0, AND keep it a value.

Ex: .69 .75 .50 .70 = 69 75 50 70.

Text to columns won't work because it has to calculate from other cells' data. This value is then used in an IF function.

View Replies!   View Related
Extract 2nd To Last Word
How do I extract the second to last word from a string of text in one cell and put it another.

View Replies!   View Related
Extract Nth From Last Word
I have a cell with a postal address.

eg -

cell a1 = 'offices, 89 high street, anstey, leicester, leicestershire, le7'
cell a2 - 'flat 41, first floor, 21 church lane, quorn, loughborough, leicestershire, le12 7zx'

i want to be able to extract the county name, which always appears between the penultimate and last comma.

I have tried
=LEFT (a1, FIND(",",a1)) which gives 'offices'
or
=RIGHT (a1,FIND(",",a1)) which gives 'le7'

View Replies!   View Related
Search And Extract
I have a list of email addresses in column A (eg: andre@mailburst.co.za , pete@ vodacom.co.za). Is there a way to ectract say only the addressesending in "@vodacom.co.za"?

View Replies!   View Related
Formula: Extract The Name
The first one contains the string "THOMAS J MALONEY 11/30/88M"
The data in the cell does not include the “” ’s (I just placed them in there so it would show the info. The string of data consists of 49 characters. I need a formula that would extract the name only so it would appear as THOMAS J MALONEY.

The second formula that I need help with is in regards to the string “TAMPA FL33615” Once again the data in the cell does not include the “” ’s. This string of data consists of 33 characters and I would like a formula so that it appears at TAMPA, FL.


View Replies!   View Related
Extract Last Name From A Name String
I have a list of names in a data set like the examle below. I would like to be able to isolate the last name so I can match the data in this data set with a data set with additional information. The only common set of data I have is the first and last names.

View Replies!   View Related
Text Extract
I know there is a formula for the following but I cant remember which one. I have a text string with words seperated by a "". Which formula should I use to display the words from the string in a cell?

View Replies!   View Related
Extract A Row From A Table
I have a table about 35,000 rows. I just sorted the table so now you can see the data by date a time of occurance. Now there is duplicate data... like almost duplicate 1 or 2 fields are different which is ok BUT I just want to extract the first occurance so EG:

765432 Data data data etc etc maybe something different here
765432 data etc maybe different here
765432 data same thing
765449 data....
765449 data...
800000 data
898427 data
898427 data etc...

OUTPUT:
765432 Data data data etc etc maybe something different here
765449 data....
800000 data
898427 data

Notice how it just took the 1st occurance and not all... I tried Advance filter but the data is all unique so it doesnt help it takes everything... I tried recording a macro but its crazy! and I did a pivot table took the first field... tried a vlookup and thought I could drag it but its getting too complicated.

View Replies!   View Related
Extract First Three Letters
How would i extract the first 3 letters of the first 2 words in a cell?

the cell may have 1 to many words in it.

I would like to exclude words like of and the...

i.e. the univeristy of washington should be uniwas


View Replies!   View Related
Extract 91 From Mobile No
I have data Like Below, Its a Contact No. of the Customer, I have 21000 Contact Number including Mobile Numbers.

View Replies!   View Related
Extract The Numbers
I'm trying to obtain a daily/monthly sales total. As you can see from the sample I've left, I have a number of different sales dep't and have to tally each one, but I have a situation where 1 of the dep't I need to keep a tally (including text) of what the amount refers to (but all in the same column, can't seperate them into different columns.......just in case of an doubts). What I need to accomplish is a formula for the following:

1- that it can recognize AND sum across the values. (TOTALS column)
2- that it can recognize AND sum down the SALES D column.

Sheet1 *ABCDEFG1DATE SALES A SALES BSALES CSALES DSALES ETOTALS22/26/2009$458.00 $23.00 $- * $20.00 Late fee + $30.00 purchase$9.00 = $540.0032/27/2009$875.00 $- * $56.00 $12.00 late fee $100.00 delinquency$43.00 = $1,086.0042/28/2009$1,235.00 $12.00 $42.00 $7.00 vis $16.00 mcd $23.00 amx$13.00 = $1,348.005SUBTOTALS$2,568.00 $35.00 $98.00 $208.00 $65.00 $2,974.00 Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Extract The URL Or Link
I have data taken from the web which includes hyperlinks. Is there a VBA technique to extract the link itself and place it in a seperate cell? IE end up with the original text that was clickable in column A and the underlying URL in column B?

View Replies!   View Related
Extract Names And #N/A From Each Other
In sheet 1i have in which Column A i have names and #N/A listed as below:

A
NAMES

Kamlesh MoreSohel Khan#N/A#N/A#N/AAvadhut Shide#N/ARajesh KumarSahil JoshiSriram V.Sachin DeshpandeNaveen kumarBabli Bunty#N/APravin KumarManoj Joshi#N/A#N/APravin Vishwanath #N/AJohnson Sudhir khanna

Now i want to extract names and #N/A from each other and want to get it printed in sheet 2 column A & B respectively using any formulae or fumctions.


View Replies!   View Related
Extract The 1st Row Of Each Duplicate Set
I have a worksheet which looks like below.
ColA ColB
1 Red
2 Red
3 Red
4 Dog
5 Dog
6 Blue
7 Blue
8 Green
9 Green

Is there a formula I can use to extract the 1st row of each duplicate set (column A having unique values, column B having duplicates)? So from above my result would be:
ColA ColB
1 Red
4 Dog
6 Blue
8 Green

View Replies!   View Related
SUM Or Lookup (extract The Sum Of Each Of The Sum Value For Each )
I am building a template which needs to pull values from an excel sheet that
will be copied and pasted into one of the worksheets in the template. These
values are always in the same columns but not always in the same row as the
length of the file varies. One of the main problems I am having is that there
these values are sums (located at the bottom of the table) but they do not
have any indicator in the first column (it is blank so I cannot use vlookup).
Also there may be multiple tables (each under the privious one) and in this
case I need to be able to extract the sum of each of the sum value for each
table.

Also, I was thinking of taking another route. Instead of looking up the
already calculated sum values I was thinking there might be a possibility to
sum the values my self and pull that value for the columns needed into my
template. The first lign of each table begins with "Asgn" and ends with "End"
and I was wondering if there is a way to sum the values between (and
including) these two markers for the columns needed.

View Replies!   View Related
Extract Numbers Only
CAn a formula/macro be provided to extract the numbers (including Decimal) from a cell value containing alphanumbers?
For eg.Down 3,492.00 INR should be extracted as -3492.00
Up3,492.00 INR as 3492.00

Please note that the numbers may be of any digit. If it contains down, then the number should be negative and if UP then positive.

View Replies!   View Related
Extract Numbers After The (/)
just got a problem with this guys, sample :

in A1 i copy and paste -> "previous / 12.25 " or " "previous/12.25"

and i want in b1 to only have "12.25".

View Replies!   View Related
VBA: Extract Data
I have a table: A1: K28879

I need only certain rows to be extracted and placed on same worksheet or a seperate one BUT

The rows to be extracted are under a heading above it which that says 9 number and Count eg: 123456789 Count. (Count is alwatys there the number vary however its always in column A)

EG:

45621234 Count
data data etc..... (i need this row)
...another row
...another row
..etc

456345678 Count
data data etc.. (this one)
more data... (not this row)

etc..

The first row under that heading needs to be extracted only.. some under that heading might have 1-15 rows it varies...

View Replies!   View Related
Extract Domain From URL?
I have a string variable which is dynamically populated with a URL value. Some examples of the URL are:

http://cartoondoor.blogbus.com/index_5.html
http://asirir.blogspot.com/
http://neuronets.chat.ru/appendix.html

I'd like to be able to extract the domain--meaning the string value between // and /.

View Replies!   View Related
Extract Folder Name
if I have a workbook sitting in a folder, how to I extract that foldername with VBA. Not the full path or the filenmae - just the folder name?

View Replies!   View Related
Extract Last And Next-to-last Entries In A Range
Assuming that the entries are text values:

For the next to the last entry:

=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)

For the last entry:

=LOOKUP(REPT("Z",255),A:A) .....

View Replies!   View Related
Extract Data From A Website
Is it possible to extract data from a website into an excell workbook using data from the workbook?

I have rough workbook that i gather data from 3 different websites to calculate a rough market value for DVDs that I buy and sell. I am good with excel but have no idea about importing data into it.

Currently I copy and paste from the sites on an as needed basis but it is a slow slow task when I have several dozen or more to do per hour.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved