Remove Parenthesis & Text Within From Range
Feb 21, 2008
For a spreadsheet that contains info with opening and closing parentheses in the cells I'm testing on, I am having problems getting multiple IF statements to work together in the same formula. I can get them to work separately in different cells, but when I try to combine them in the same cell, I get the #VALUE error. Here is what I'm working with:
In cell G7 I have the text: Fort Fraser (1)
In cell H7 I have the formula: =IF((RIGHT(G7,1)=")")*AND(MID(G7,LEN(G7)-2,1)="("),LEFT(G7,LEN(G7)-4),G7)
In cell G8 I have the text: Fort St. James (11)
In cell H8 I have the forumla: =IF((RIGHT(G8,1)=")")*AND(MID(G8,LEN(G8)-3,1)="("),LEFT(G8,LEN(G8)-5),G8)
Both formulas work correctly by discarding the parentheses and everything in between, as well as the space before the opening parenthesis. As you can see, the first formula will work with a single digit in the parentheses, and the second works with two digits. It's just when I try to combine the two tests in the same formula (editing the second one so it also tests on G7) that I get the #VALUE error. I have tried using OR to combine the two tests.
View 7 Replies
ADVERTISEMENT
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
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
Nov 24, 2008
Suppose I have data in a range that was imported from "somewhere". Some of the data is preceded by team rankings in parenthesis...like so:
Alabama
(21) Boston College
Notre Dame
(10) Oklahoma
Texas
Florida State
(2) Florida State
I want to strip the data of the team rankings, (as well as any brackets and spaces before the team name). Is there a macro I could write to do this?
View 5 Replies
View Related
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
May 28, 2014
We have a spreadsheet of our customer's info, to send to our collection agency; when we export it puts parenthesis around the area code and adds a hyphen in the number.
Also it puts commas in the figures. And I need the slashes gone from the date - is there a simple way to do this?
I tried formatting but it doesn't take out the () or ,
View 9 Replies
View Related
Aug 21, 2009
macro find parenthesis and remove string in every other cell in Col.A
The following formula works but is tedious to apply to every other cell:
=LEFT(A3,FIND("(",A3)-2)
Data Example:
Cell Value A2 = hello world I am A2 (this is an example)
Cell Value A3 = hello world I am A2
Then Delete the entire row A2 and apply this to every other A.Col.cell with the original data.
View 9 Replies
View Related
Sep 1, 2007
I have a database which contains sometimes
product(1)
product(2)
product(3)
product(4)
etc...
I would like to keep only the text before the ( starts
View 5 Replies
View Related
Mar 2, 2008
I have a column of data; for each line of data I have something like ABCDEEast Anglia, ABCDFFarnborough. The text at the start is standard and all cases of East Anglia will have ABCDE prior to the East Anglia. Is there an easy way [aside from replacing] to loop through 1000 data points and replace the long method with a shorter concise version (i.e. East Anglia only). I have attached what I mean
lowtusmaximus
View 7 Replies
View Related
Oct 12, 2013
Initially I'm simply copying a data table from a web page using "Ctrl + A" then "Ctrl + C", and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)
But I keep finding what looks like double-spaces after some of the important text within the Range of cells I'm working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.
Unfortunately, it's not consistence as to how many spaces trail the text I need. Sometimes it's only one space, sometimes its two spaces ?
So far, I've had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don't work all or seem to give any consistent results. E;g; TRIM, CLEAN
I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?
The start of my code reads:
Code:
Sheets("Data").Select
Sheets("Data").Name = "Old Data"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = "Data"
[Code]...
' At the moment I'm using to select the pasted range I want to work on: Range(Range("C46"), Range("C46").SpecialCells(xlLastCell)).Select
This is where I need a code to work on the new Data sheet and remove all the trailing characters.
MsgBox "All data cleaned successfully !", vbInformation + vbOKOnly, "All Done"
View 3 Replies
View Related
Dec 4, 2007
I have a formula which I've been working on which utilizes a lot of defined names and INDIRECT functions. I have been parsing out the segments of the formula to figure out which section is incorrect, and I think it is from the last SUMPRODUCT on. I think I have a parenthesis issue.
=IF(NSAR_1="","",ROUND(SUMPRODUCT(INDIRECT("AcctNmbr_NSAR"&MID($A13,7,2))="1005000300")*(or((INDIRECT("ShareClass_NSAR"&MID($A13,7,2))="TF"),(INDIRECT("ShareClass_NSAR"&MID($A13,7,2))=MID(NSAR_1,7,3)))),INDIRECT("SubsReds_PIS_NSAR"&MID($A13,7,2)))-SUMPRODUCT(INDIRECT("SupersheetDte_NSAR"&MID($A13,7,2))=RptgDtePrior_NSAR1)*(INDIRECT("SubRedInd_Act_NSAR"&MID($A13,7,2))="S")*(or((INDIRECT("ShareClass_NSAR"&MID($A13,7,2))="TF"),(INDIRECT("ShareClass_NSAR"&MID($A13,7,2))=MID(NSAR_1,7,3)))),INDIRECT("NetAmtBaseReinvests_NSAR"&MID($A13,7,2)))-SUMPRODUCT((TransType="REINVEST")*(ActiveStatus_Act="Y")*(or(INDIRECT("ShareClass_NSAR"&MID($A13,7,2))="TF"),(INDIRECT("ShareClass_NSAR"&MID($A13,7,2))=MID(NSAR_1,7,3))),NetAmtBase_Reinvests),0)))+F13)/1000)))
View 9 Replies
View Related
Jun 12, 2014
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C
For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
View 3 Replies
View Related
Jul 24, 2014
Right now, I am using an index match to find a name. It returns it correctly, but is there something I can add to formula to to return the name, and put the name in parenthesis. Example: currently it returns name Chile, but I want (Chile).
View 4 Replies
View Related
Jan 6, 2007
In line me.controls(0) what (0) stands for?
I thought it's for tabindex but after experiment it's not.
View 9 Replies
View Related
Oct 2, 2012
I am looking for a vba macro that will replace everything that is within an open and closed paranthesis - within a cell.
For example:
Column A
Column B
Coke
(301) + (302) + (303)
[Code].....
View 4 Replies
View Related
Jul 20, 2009
I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.
View 9 Replies
View Related
Jul 12, 2002
I have a column in which the cells contain values such as "012321 BGH YRK". Is there any way you know of that I can remove just the text from every cell and keep the numeric values? A mass operation because there are literally hundreds of these cells. I am using Excel 2001.
View 9 Replies
View Related
May 15, 2014
"your formula is missing --) or (. Check the formula and then add the parenthesis in the appropriate place. The following is one long string. I broke it up to make it easier to read.
=IF(OR(ISNUMBER(FIND("J",M117)),K114,
OR(ISNUMBER(FIND("S",M117,)),K112,
OR(ISNUMBER(FIND("HOH",M117,)),K117,
OR(ISNUMBER(FIND("MF",M117)),K113,
OR(ISNUMBER(FIND("QW",M117)),K116,
OR(ISNUMBER(FIND("B",M117)),K115,0
View 3 Replies
View Related
Apr 22, 2014
i have a worksheet with imported data. it is a chess game notation with comments. is it possible to separate the comments from the moves? the comment part comes in parenthesis. whatever comes in paranthesis has to be separated to the next column. I have given an attachment in which on sheet 1 , is the raw data. on sheet2 i have shown the result.the sheet has to be so edited that it should look nice.since i have separated the comments manually it does'nt look nice.I have separated the comments from the first table of contents only.
i usually don't use the buttons. instead i go for searching the code in the macro dialog. did this time also. but i could not find it there in the list of codes. i even changed the word"private" to option explicit with no positive result. what should i really do to run the macro? i always want that macro is named in the dialog when i click on othe macro-code groups.t
View 6 Replies
View Related
Oct 27, 2005
The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
View 10 Replies
View Related
Jul 28, 2006
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 5 Replies
View Related
Jun 9, 2007
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 9 Replies
View Related
Dec 15, 2009
I use keyboard commands whenever possible. They are much quicker than the mouse.
For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.
I like to see negatives in red with parenthesis.
View 5 Replies
View Related
Apr 23, 2008
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 7 Replies
View Related
Jul 2, 2014
I am formatting total dollars to millions using the custom format $0.0,,
It displays negative values such as $3,100,000 to -$3.1
How do I get the negative values to display in parenthesis such as $(3.1)
View 3 Replies
View Related
Sep 11, 2009
How do I remove a Range Name in order to respecify a Range using the same Name?
View 4 Replies
View Related
Jun 24, 2009
I have two txt file where the report is being generated but importing it in excel and using text to column distorts the data plus it has a header which repeats on every page... how can delete those header and tidy the sheet up. I have attached the txt header example.. Any help on this.
View 14 Replies
View Related
Aug 13, 2009
I need to format C2 so that it removes all text that comes after a dash in B2, but if no dash exists, then it returns the text: Parent. For example, here are 3 values in B2, B3, and B4:
1234-s
1234-m
1234
In C2, C3, and C4 I'd like to have the following values returned based on the above values in B:
1234-
1234-
Parent................
View 2 Replies
View Related
Jun 24, 2014
I'm working with a large range of data in single columns and I want to remove all text characters (a-z) from the workbook. There are some cells which have text characters with numerical characters so I'm struggling to find a way to isolate them.
View 6 Replies
View Related
Apr 7, 2006
In a cell there is text and numbers, example: ABC123. In an other cell I want
to show the numbers only (123).
View 9 Replies
View Related
