I have an excel spreadsheet with a column of part numbers. The part number consist of a prefix, base, and suffix which is separated with dashes. For example 1C3Z-6211-AA. I am wanting to copy the base number only to another column. The problem is the prefix can be 2 to 4 digits, the base can be 4 digits or longer and the suffix can be 1 to 4 digits long. Is there a way to copy only the number between the dashes?
View 3 RepliesI 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 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 need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?
View 6 Replies View RelatedI have a URL in column B and want to copy everything to the right of the "/" forward slash to thw cell on right in column C, what formula can I run to get this accomplished, URL length may vary.
(Column B) (Column C example)
[URL] .......
I want the VB Code to do the following;
For each row in Column C, if the text starts with "Calls from service no", it must extract the last word and paste it into corresponding row in Column A. So if the macro finds a cell in Column C starting with "Calls from service no..", then the last word (the last word will always be a number and is seperated with a space) of this cell must be pasted in cell A45. So it must continue for all occurances in Column C. Sample of text in cell = "Calls from service no 0431324345" and "Calls from service no 3242134254" etc.
I also require similar, but this time it must extract as shown below.
If text is found in Column C starting with "Local" or starting with "Long Distance" or starting with "Mobile", only this text, ie "Local" or "Long Distance" or "Mobile" must be pasted in Column B.
It can be 2 different macros that I can run.
Here's the situation (an example):
I have sheet1 that contains the following information
Column A Column B
company1 - book value 0,5
company1 - earnings 0.2
company1 - R&D 1111
company1 - total assets 200000
company2 - book value 2
company2 - earnings 333
company2 - total assets 12
So column B contains only numbers and note that for company 2 I do not have any numbers on R&D.
I have in sheet2 only once the company name. I would like now that if column a contains the text "book value" to have the value of column B pasted in sheet 2 column B. Same for earnings to be pasted in column C. R&D in column D and if R&D is not present for that company then the value should say something like unknown.
I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:
Current List ----> Column A -->Column B
Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE
Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith
H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus
Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD
Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA
Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel
Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong
Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D
Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder
Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar
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 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
*TxSTATUS
*ON
*OFF
*TxBANDS
EG
I2 - Variable = B
*TxSTATUS will become *TBSTATUS
I2 - Variable = A
*TxSTATUS will become *TASTATUS
But " *OFF " should not change.
I can change *TxSTATUS to *TBSTATUS but then I can change *TBSTATUS to *TASTATUS
Please see attached example file as it shows it better than I can explain it.
Let me try to explain.
I know that I can do this to fetch a cell entry from an external workbook
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 RelatedIn 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 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 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 Replies View Relatedi 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
how 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)...