I want to delete part of a cell in Excel. For example if a cell had the data "BlueGreen" how would I delete the "Blue" bit just to leave the "Green" bit behind? Surely there is an easier way then physically deleting the first part in each cell? I have about 4000 cells to work on!
View 9 RepliesThis is similar to a previous post, in July, which was masterfully solved. I have tried to adapt the previous script, without success. Therefore,
I have copied data from a screen and entered it into Excel 2007, which is attached as 'snohomish sample.xlsx The data starts out (sheet 1) in 8 columns (A-H), and only in the odd rows (1, 3, 5, ...) What I wish to do is the following:
Move 'sheet 1 column B' to 'sheet 2 column A'. This is a date which needs to be in mm/dd/yyyy form.
Move the last 14 digits in 'sheet 1 column F' to 'sheet 2 column B'. This needs to be text so that I don't loose any of the zeros.
Move 'sheet 1 column E' to 'sheet 2 column C'.
Leave 'sheet 2 column D' blank as I will be entering a currency amount after running the module.
Move 'sheet 1 column A' to 'sheet 2 column E'. This also needs to be text as I can't loose any zeros or have it in scientific format.
The rest of the data on sheet 1 is not needed on sheet 2.
I will be entering data in 'sheet 2 columns F-K' after running the module.
I am attaching 'snohomish results.xlsx' which shows what I hope the results to look like (note the sample results are on sheet 1 of a different file).
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).
=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
View 2 Replies View RelatedI have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.
Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
i have a column of data part number part text e.g 7w1,8b1 in the next column i want to write a function that will look at the other and dependant on whats in it place a word e.g. for 8b1 i need it to look at the "b" and enter beauvale if theres no "b" but a "w" enter wollaton, i have tried an if function and used "*b*" but it doesnt seem to like wildcards ....
View 9 Replies View RelatedI have some cells with text entered in column A.
These cells contain a time reference either in '09:30 - 16:30' format or just an 'AM/PM' format.
What I need to do is to populate column B with the part of the string that comes AFTER the times in the text (either in hh:mm format or AM/PM format)
Example
336 Data 2012 Classroom Induction 2012/08/28 09:30 - 16:30 data about this ------> data about this
336 Information about this 2013/04/04 09:00 - 13:00 Information 2 ------> Information 2
336 info CHS Suffolk 2013/05/07 PM DRC, BSE ------> DRC, BSE
364 information 2013/02/12 AM DRC, BSE ------> DRC, BSE
I cannot get this to work and cannot use Text To column as there is no character I can use to split it.
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2
The catch is that I then need to be ablt to change that character when I2 changes.
Coloum C
*VER
*T’x’STATUS
*ON
*OFF
*T’x’BANDS
EG
I2 - Variable = B
*T’x’STATUS will become *T’B’STATUS
I2 - Variable = A
*T’x’STATUS will become *T’A’STATUS
But " *OFF " should not change.
I can change *T’x’STATUS to *T’B’STATUS but then I can change *T’B’STATUS to *T’A’STATUS
Please see attached example file as it shows it better than I can explain it.
In one cell I have multiple value:09/16/2013, 09/16/2013, 09/16/2013, 10/15/2013
I want to use the first read which is 08/22/2013. Not always i have multiple dates in my table.This is part of code that I am working on.What I usually do, to get the last value when I have multiple reads is:
I have select the cell C1, the multiple reads(dates) are in the cell B1, so i need the last read(10/15/2013) ;i use this code:
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],10)+1"
SO now I want use the same logic but i want to take the first read/date. Also here the cell from where I want to get the value is selected.
Above the multiple reads were in the cell B1 and cell C1 was selected before performing the code, now the code is in the cell B1 and B1 is also selected.
I tried to do ActiveCell.FormulaR1C1 = "=Left(RC[-1],10)" but I rich the value in the cell on left which is A1. I want this for tomorrow since I have something to present.
If I have some data in a cell (let's say D1) such as "m-3,s-2,xl-4", what formula can I use in say cell D2 so that if I find the characters "s-" in Cell D1, then it will return the value "2".
Similarly, in say in Cell D3, I would then want to use the same formula but modify it so that if the characters "xl-" are contained within Cell D1, the formula will return "4"
Just "upgraded" my Excel to the 2007 version. In the old Excel, I could superscript part of a cell by highlighting the part I want, then going to "Format" and "Superscript."
In Excel 2007, it doesn't work. Yes, I can highlight the part of the cell I want to superscript, I can right-click and select "format" and "Superscript", and it even then shows as superscripted in the cell. That is, until I hit enter or move to a new cell. Then the superscript is no longer there. The cell is in "general" number format.
By the way, it did work in another cell: mg/l3 (3 superscripted) works. 742 (2 superscripted) doesn't work.
I have a cell that has this
ALG-g30
And I have another cell that looks like this
ADL G28 Clear Lamp Chimney 8-1/2 inches
I want the cell to look like this
ADL G30 Clear Lamp Chimney 8-1/2 inches
Basically I want to take the g30 and convert the "G" to uppercase and add it with the "30" to the other cell..
I need to do that with 40 rows..
How would I do that?
I copied the rows here down here --- [URL] .......
I would like to be able for a user to enter something in a cell (E14) and then press Find and it open a internal web search.
[Code]......
In this code %1234% would be in E14. How do I make the hyperlink change based on what is in cell E14?
The %2A has to stay in both beginning and end of the text in E14.
Column B on the spredsheet gets the Part Description vaules, we copy and paste from Customers excel and unfortunatly some descriptions contain 3asteriks in a row (***) and I need to import this into Access later on and Access will not except asteriks. Find and replace doesnt seem to work like i hoped , it replaces everything in cell not just part of it . If someone has a Formula that will work that would be ok too, it does not have ot be VBA
EXAMPLE Part Descriptions:
RADIAL TIE DOWN LUG (4/500's) *** Formed
FINES V-PLOW/ SCRAPER MOUNT ANGLE; Rev. A ***Formed
I only need the asteriks deleted
I have a few cells which look like this: ...
View 6 Replies View RelatedI have to perform a v-lookup to return a value but the problem is the cell I am looking up begins with but does not exactly match the the data in the table.
For example I want to look up sheet 1 cell A1 which is "Compass" in the table in sheet 2 column A and then return the associated value in column B. It is referenced as "Compass Group" in the table so I do not get a value returned if I use the exact matching function of the vlookup (i.e. using "false") but if I use the approx it one (i.e. "true") then it returns "Compagnie" which is the name listed before "Compass Group".
Basically I want to perform a vlookup function using a cell that begins with but not exactly matches a sting of letters in a table.
I have the following formula in my workbook;
=SUMPRODUCT((A2:A8702"")/COUNTIF(A2:A8702,A2:A8702&""))
The data in the workbook is imported automatically and the range 'A8702' is different every time the data is imported, although is always in column A.
Can I use the value of a cell to determine the range?
So I have used this to get the ast used row number;
=MAX(ROW(A:A)*(A:A""))
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either
SD1, SD4, BDO, BD (not neccessarily in Caps)
and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1
or
BDO xyz. when macro runs G2 returns BDO
i have a array table
all the rows of data get a unique file number , no duplicates.
the file number format is 1.01, or 241.00, or 5132.03,etc.
all numbers to the left of the period symbol are "file info" reference
all numbers to the right of the period symbol are "bills" found inside the file #.
there can be any amount of file #'s, always starting from 1 thru as many as there can be (apprx. thousands of files). and the bills can be from .00 thru .99
my search field ($r$9), need to search or find only the exact match to the left of the period.
NEED to search for file 1, i need the array table to kick back only the file numbers 1.01 and 1.65, not 241.00 not 3.01.
how can i search only the text before the period to the left and exactly what is entered into my search box?
I have written a code using VB for extracting data from notepad to excel and copying the certain data like date in other cell. I need to extract a particular value from the cell where data is copied to another using mid or any other function in VB.The code is as follows:
Private Sub CommandButton1_Click()
Call data
Call compare
End Sub
Sub data()
Dim fs, f, ts, s
Dim r As Long
Dim r2 As Long
Dim e1, e2 As Variant
Dim i As Integer
Const ForReading = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile("C:Documents and SettingsAdministratorMy DocumentssheetalCingularStatus .txt").....................
I have a question regarding searching in cells for a value, and returning corresponding data. This is what my workbook looks like:
Sheet1, cell A1 contains value "D600"
Sheet1, cell A2 contains value "V-1234"
Sheet1, cell A3 contains value "DB23"
Sheet1, cell B1 empty
Sheet1, cell B2 empty
Sheet1, cell B3 empty
..........................
1. search each cell value form Sheet1 column A, in Sheet2 column B
2. when a match is found, return the corresponding value of column A from Sheet2....
I use this code to "search" a value in columns A or B and if found to select the row the value is in.
Trouble is if I look for say value 11 (the code finds 1) I understand what happens but need your help to fix it hoping to keep it as simple as it is. (I cannot use filters)
Dim wht As Variant, Found As Range
wht = InputBox("Searchfor")
Set Found = ActiveSheet.Columns("A:B").Find(what:=wht)
If Found Is Nothing Then
MsgBox "Not found"
Exit Sub
Else
Let me try to explain.
I know that I can do this to fetch a cell entry from an external workbook
I need to copy information in one cell to only part of another cell. For example:
C1:Office Name N1:Path
C2:Houston Office N2:OU=Houston Office,DC=contoso,DC=com
Trying to copy from anything in cell C2 to after the = sign before the comma in cell N2.
I have just generated a report of current customer numbers for our company. Unfortunately it reports it as "162 (162)". No idea why the program does it like that because it now makes using VLOOKUP very difficult.
What I want to do is copy the "162" part of the cell and paste it into the adjacent cell to make it easier to manipulate the worksheet. All of the customer numbers are in column D if that makes a difference.
It's difficult to explain, the attached shows the situation better. Basically there's a picture that is somehow linked to a cell. But, you can update the picture or the cell and the other one gets updated.
View 3 Replies View Relatedhow I can check if a cell contains part of a value from a list? Do I need a formula or VBA? (Both would suit)
Example:
A1= BLABLA Apple BLALBA
A2= BLABLABLA Orange BLA
List in C1:
C1= Orange
C2= Apple
If it contains one of the word, I need the function to return the word...
What I have tried:
Search formula
Match formula
VBA with "If...Like" statement
Keeping in mind that the key word Apple/Orange in A1 or A2 can be located anywhere in the "sentence" (could be after BLABLA or BLOBLOBLO or BLU etc...)
This is something I would then apply to a range (I suppose if it requires VBA, then with a loop which I would be able to manage)...
EDIT: Cells in A1/A2 will never contain two different values from the list (it is either Apple or Orange)...