Selecting Specific Strings From A Cell
Oct 17, 2008
I'm using Excel 2003 and I have a small problem using text functions. My problem is that in column 'A' I have a name and their year next to it between brackets, like so: Wright Jeffrey (1PBSO)
Now what I have to do, is make two new columns where I extract the surname (Wright) to one columns and the first name in another column (Jeffrey), the "(1PBSO)" doesn't have to go anywhere. Now I wouldn't have a problem with this normally but in the list I have names like 'Van Tongerloo Johan' where VAN TONGERLOO is the surname and JOHAN is the first name. For the first name (Wright Jeffrey (1PBSO)) I just used the LEFT function and then searched for the first space in the string but that doesn't work in the second example anymore.
View 5 Replies
ADVERTISEMENT
Mar 10, 2014
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
View 1 Replies
View Related
Jul 2, 2014
I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)
Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select
[Code]...
The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.
View 5 Replies
View Related
Jan 4, 2010
I have a combination of letters and numbers in a column. For example.
xx1234567x
1234567xx
xx1234xx1234
I need a formula to tell whether they begin with two letters and have a certain number of numbers then end in letters. Or have letters in the middle. Sorry Im so vague, I just need a start in the right direction. Ideally combined with an IF statement.
In english (If two letters afetr nine numbers then yes)
View 9 Replies
View Related
Jan 1, 1970
This doesn't work
View 13 Replies
View Related
Jun 26, 2008
Need to write a Macro to only extract numbers from text, characters and numbers fields. I would like the numbers to be extracted in column B, C & D. I am only interested in numbers that begins with 200's, 800's and 4500's. see the attached file.
View 3 Replies
View Related
Jan 6, 2014
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
View 12 Replies
View Related
Nov 12, 2008
I have a spreadsheet with Employee details and each employee has a Indicator and a Score.
I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.
If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....
View 6 Replies
View Related
Oct 2, 2009
I'm trying to clean-up a huge bulk of data. What I want to do is automatically select all rows, where column K contains text value "Closedloss", and have all these rows cut/pasted to Worksheet "ClosedCases". The second is similar to the first, but I want to select all rows where column J contains a value of 60-80, and have all these rows cut/pasted to Worksheet "MidRange".
View 2 Replies
View Related
Apr 7, 2009
I'm trying to find the best solution for this problem. I have an excel sheet that contains over 2,000 rows of data with 8 columns. In this sheet, I need to be able to select entire rows that look for specific names in column E which is titled "originator".
The name format looks like this:
Selzer, Paul (TEM)
Wathen, Charles (TEM)
Chapman, Steven (TEM)
etc
I have a total of 15 names that I need excel to look for in this entire sheet. What are my best options for this? I tried conditional formatting, but that does not work that well, as I would have to manually enter in each name.
View 10 Replies
View Related
May 26, 2014
I am looking for a way to select specific columns in excel that have specific characteristics in the header titles. For example, I have a very large worksheet with columns that specify the year and month (ie. "1900-8"). The years range from 1900 to 2011 and each year has "-1" to "-12" to represent the months of the year. Specifically, I am wanting to pick out all of the columns ending in "-8" (August).
View 14 Replies
View Related
Jul 25, 2006
How to get Excel to select all cells within a specific column that contain a specific character. In this instance, I need to select all the cells which contain a comma...
And even better would be if I could get Excel to not just select all the cells in a specific column containing a comma, but each of the rows in which those cells reside.
View 9 Replies
View Related
Dec 5, 2009
I have a output file that is collected time stamped data. How do I select the rows of the first time stamped collection. So, if the first row is stamp with 03-Jan-2009 23:59:00, I want to copy all the rows with that time stamp below the first row and stop when the time stamp starts at 03-Jan-2009 00:00:00. Attached is my sample sheet.
View 4 Replies
View Related
Aug 25, 2009
I have a Lookup table with a Group and Subgroup field. I have created a range for the Group and also for the Subgroup. On my main page, I have created a list option which allows the user to select the items under Group (Day, Colour and Time).
What I want next to do is based on the primary selection for Group; I want it to filter it on the specific Subgroup. For Example,
The User selects Colour from Group, the Subgroup will subsequently only show the appropriate selection which is Green, White and Yellow, and no further selection.
View 4 Replies
View Related
Aug 28, 2013
know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.
View 3 Replies
View Related
Sep 13, 2013
I have x number of sheets in a workbook, and I am looking to copy sheets a, b and c into a new workbook that I will create.
The a, b and c are in no order, or consecutive (although they could be). I want to be able to count the number of sheets in the workbook, and traverse through that to find sheets that I need, and select them to copy over to a new workbook.
View 1 Replies
View Related
Dec 19, 2006
is there any way to open a workbook and select a sheet within the book without having the entire name?
I've got a unique workbook and worksheet identifier but don't know how to use wildcards to get my macro to open the right workbook.
View 9 Replies
View Related
Dec 13, 2008
I have a worksheet which is used by multiple users at the same time, at the end of the day I can have upto 10 files. The layout of all the files is the same, same number of columns and rows, the only difference would be the file name eg:Test1.xls, Test2.xls etc. information in test1.xls will not be any other file. At the end of the day, these files are emailed and saved in a particular folder.
What I need is get information from all the files saved in a particular folder and keep columns which have data. Additionally, if there is no data for any columns after combining the columns to display an alert. The final file should have information with only data in individual columns and arranged in the order such as Center 1, center 2, Center 3
Eg:test1
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 center 3....upto 30 centers
1 4
2
3 5
test2.xls
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 Center 3.....upto 30 centers
5
The final file will look like this
Finaltest.xls
Column A Column B Column C.....upto 30 columns
1 4 4
2 5
3 5
View 9 Replies
View Related
Jan 11, 2005
I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.
I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.
View 9 Replies
View Related
Nov 15, 2013
Whenever I try to use Sheets("Volumes").Select or .Activate on a specific worksheet, my macro code will immediately terminate with no error message. Iv'e used F8 to step through several modules and found that this happens every time it hits that line. The wierd thing is that i can select this sheet when screenupdating is off and I select it from a called subroutine. No other worksheets in this workbook are having this problem.
Additional info : using Sheet1.Select will select it with no issues but I don't want to go this route since it's a workaround and not a solution to somthing that should work.This problem occurs in several modulesEverything used to work fine and just one day it decided that i couldn't select the "Volumes" sheet in VBA anymore.I can click on the sheet no problem and it is not protected or hidden.
View 1 Replies
View Related
Dec 20, 2012
I have to correct a spreadsheet that takes a bunch of values and creates an average.
=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%
I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.
So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.
I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%
It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.
So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try
=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%
It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.
The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.
View 5 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Jan 11, 2012
how would you do the following in excel 2007:
"SUM numbers within a cell based on unique strings in another cell".
For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16
700david
700 david
454john
50buch
40daniel
2sam
2sam
129mike
16steve
View 9 Replies
View Related
Aug 18, 2006
I have two adjacent strings in a CSV file that I'm importing which were originally intended as a single piece of data. The format of the strings are:
5'-3 3/4" (Yes, it's a good old imperial length measurement)
Right now, I have (5'-3) in one cell, and (3/4") in the next.
I'd like to try to append the second string (3/4") to the first in a single cell. Is there any way for me to do that using VBA?
View 9 Replies
View Related
Jan 20, 2009
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
View 3 Replies
View Related
Feb 18, 2014
I'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.
I'm using control + F to open the search box.
It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.
So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.
It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.
View 3 Replies
View Related
Jul 4, 2009
i have in a cell a Text like this..
Chicago (TZGUG)
i tried a formula.. like this..
=LEFT(F3;LEN(F3)-1
to remove only ) ..
but how can i remove the ( too ..
and also Chicago
so the result in a cell would be TZGUG
View 11 Replies
View Related
Jun 23, 2014
I am attempting to count up the sum of names that will be submitted, through a web form, in a single cell.
Example:
Cell B1 will have a bunch of names submitted to it. let's say 10: Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman. (these text strings will be all in one single cell)
I will have a list of names that are correct to compare to: Montana, Brady, Bradshaw, Manning. I can put those in its own cell to compare to. (C1)
How do i get the sum of 4 from B1 cell? If B1 only has 3 of the 10, how would it return the sum of 3?
Example:
A1 B1 (Submitted cell) C1 (List to compare to "right answers") D1 (sum from B1 compared to C1)
user 1 Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman Montana, Brady, Bradshaw, Manning 4
user 2 Marino, Elway, Namath, Montana, Manning, Brady, Favre, Aikman 3
This is I am sure a very newbie question, but I couldn't find anything on multiple text strings within one cell, and counting a sum of those.
View 14 Replies
View Related
Jan 4, 2014
String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct
1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"
2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".
I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".
below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row.
=IF(summary!$E8>15000,SUM(F3-summary!D2),0)
=IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))
=IF($E14<15000,ABS(F14-summary!D13),IF($E14>=10000,0,(F14-summary!D14)))
View 6 Replies
View Related
