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
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
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
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
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
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
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 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
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
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
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 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
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
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
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 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 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 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
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
VB To Extract Comments And Value To New Sheet
I would like to use a macro to automatically extract the comments in Sheet 1 ( those comments are Customer Names and Their addresses ) to a new Sheet 2 which will print shipping labels.

I have manually created the first three labels on Sheet 2 to show what I want to accomplish. The tricky thing is, that some Customers receive 2 or more artworks...

View Replies!   View Related
Extract String From Cell
In the following rows I want to extract the string that ends in K (ie everything after the final space). But I don't know the starting position or how long the string will be.

C2 9K
C1M (RB57) 4K
C2 4K
C6 FM (RB78) 120K
C2 9K
C1M (RB57) 3.5K
C2 (RB68) 12K

Can this be done?

View Replies!   View Related
Extract Numbers Before Text
I need some code that extracts everything after the last number in a cell. For example, in A1 I have

Flat 3 45 New Road

and need to rearrange it to get

Flat 3 45

into another cell. I've found this code;

View Replies!   View Related
Extract Data From One File To Another
I have a report that is downloaded off a company database and creates a report that is attached.
Is there a way to create a macro to extract some data that I need and leave the rest:

I need to keep and everything in the assigned column
PB ID (A3), Issue (B3), Group Code (J3), Group Name (K3), Sub group id (L3), Sub Group Name (M3),Subject ID (L3), Subject Group Name (M3), Subject ID (N3), Subject (O3), PB Status (P3), CPM Report Name (T3), CPM Profile Name (U3), Profile State (V3).

The worksheet's name changes so I can't reference the worksheet. Attached is a copy of what I get when downloaded.


View Replies!   View Related
Extract All Sheet Names
I have a workbook with 40 or so worksheet tabs in it. How do I extract the names of these different tabs into, lets say Column A??

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 Hourly Data
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.

View Replies!   View Related
Extract Data From Columns ..
how to extract all members of group of data (column) - for example data is (2,4,2,5,6,6,2,6,5,5,2) and to get as result only members of group (2,4,5,6)?

View Replies!   View Related
Extract Alphanumerics From String
I have many strings of arbitrarily length. Each string always
has one number (0-9) component and one alpha-character (A-Z) component.

The order of the components in the string is entirely random.

Sometimes numbers come first and sometimes letters/characters.

Also, the length of each separate component is also varies and
there is no fixed rule to how long it will be each time.

Examples are:

String: RGH45 Alpha: RGH Num: 45
String: 4589THF Alpha:THF Num: 4589
String: FGGFFF56464645464 Alpha:FGGFFF Num: 56464645464
String: sdgdfgdfg874645 Alpha:sdgdfgdfg Num: 874645
String: 54sfsdfdsf Alpha:sfsdfdsf Num: 54
String: s54654646 Alpha:s Num: 54654646

etc..

I want to have two strings. One called Alpha and One called Num
I want to have the alpha-character component to be extracted from the string
and set to Alpha and the numerical component to be extracted & set to Num.

How/what is the Excel Macro VB code to accomplish this from within the macro?

View Replies!   View Related
Formula That Extract The Numbers
Need a formula that will extract the numbers in Col C into this format
C2 2,2,1,0,0,0 to 221000. Thanks for all suggestions. I can copy the numbers into Notepad and do a replace, but there has to be a better way in Excel.

******** ******************** ************************************************************************>Microsoft Excel - FL LOTTO 6-53.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC2=
CDEF22, 2, 1, 0, 0, 0 32, 2, 0, 1, 0, 0 42, 2, 0, 0, 1, 0 52, 2, 0, 0, 0, 1 62, 1, 2, 0, 0, 0 72, 1, 0, 2, 0, 0 82, 1, 0, 0, 2, 0 92, 1, 0, 0, 0, 2 102, 0, 2, 1, 0, 0 112, 0, 2, 0, 1, 0 122, 0, 2, 0, 0, 1 132, 0, 1, 2, 0, 0 142, 0, 1, 0, 2, 0 152, 0, 1, 0, 0, 2 162, 0, 0, 2, 1, 0 172, 0, 0, 2, 0, 1 182, 0, 0, 1, 2, 0 Sheet9
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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