Is it possible to separate the parts of a mixed number? I have a cell in which is generated a decimal number. I can convert this to a mixed number. What I need to do from there is extract the whole number and the numerator.
View 11 RepliesI 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".
The problem I have is I have a cell that says "170.51CR" and I want to extract the number (to perform an operation) without changing the cell.
The reason why is that data is from a website which updates so I can't adjust it.
Eg if I have "170.51CR" in cell B3 and I want to times the number by another cell When I times that value it return an error so I just want to operate the function on the 170.51
I am trying to extract a 7-consecutive-digit number from a mixed string (letters and numbers). The only condition is that the number has to start with a "7" or "6" and be exactly 7 consecutive numbers long. My function only evaluates for one and one within the other but not both. It's faulted because it only looks for the occurrance of "6". rCell is the string range.
Function ExtractNumber(rCell As Range)
If Len(Mid(rCell, InStr(1, rCell, "6"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "6"), 7)
ElseIf Len(Mid(rCell, InStr(1, rCell, "7"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "7"), 7)
End If
End Function
I have a column of data that is given to me that is a mix of letters and numbers and I need the numbers to have leading zeros, they must all be three digits. The data has either 3, 4, or 5 letters followed by numbers 1 through 999. Example: ABCD7 I need to change it to ABCD007. I am using Excel 2004 for the Mac.
View 9 Replies View RelatedI am having some difficulty with a macro. I have a column that contains a series of inventory numbers. I need to break it up into three parts and then have each part put into a new column. Here is an example of the data:
Column B = list of inv no.’s such as “ABC123.45” I need to divide it into three parts and put each part into a new column … so a new column X would get the “ABC” part, a new column Y would get the “123” section, and a new column Z would get the “45” ** ( I don’t need the decimal between 123 and 45) .
I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.
View 3 Replies View Relatedas you can see in the picture;
it is possible to separate numbers from Column A into each column of B and C?
I have the following text, in which I need separated into three columns.
Column A) Company Name
Column B) Address
Column C) Phone Number
I was thinking perhaps I could replace "-------" to just one "-" and use that delimiter to separate the phone number.
Then Use *** Company Name Here *** --> *Number Variable* and filter the rest..
But I don't see it being able to work.Here is the text I'm looking to format (Mind you there is about 8,000 Records.
And, it's all formatted like below. (Company Name, phone number being on different lines and phone number being separated by dash's
[Code].....
I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...
Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith
In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.
In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.
VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.
I have attached the file : Dates.xls‎
I want to be able to add a column with mixed numerals. For example: The sum
of A2:A34 with the values in the cells being 1B or 1F or so.
For example if you want C23:C99 and column H23:H99 as well
Meaning a For each loop would display contents of
C23
H23
C24
H24 etc.
I have a mixed string (i.e. containig character and numbers.... but the format is same PAR1 or PAMR1 or PR10 it can be 4 characters or five) i want a formula or a macro which splits it in two parts one containing characters and the other one containing nos.
View 9 Replies View RelatedA column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
This is what I need to be doing:
Write cell K5 as a relative reference, absolute reference, mixed reference with the row varying, and mixed reference with the column varying.
How do I do that?
Counting with text and symbols mixed in. This is what I'm wanting to do:
For example:
How can I make Excel create a column with only email addresses from a document that has all contact data of each customer in it? The column that contains the e-mail addresses also contains phone numbers at present...and many blank spaces in between addresses.
View 9 Replies View Relatedi've got supplier list on the excel file and need to insert two blank rows under each supplier name. the two blank rows need to be inserted no matter whether the supplier may be occupying one row or 100. for example
from this
a ltd
a ltd
b ltd
b ltd
b ltd
x ltd
m ltd.........
Just wanted to do some work onto the form below, move the numbers out from the mixed colum and put in a new colum, then delete all the rows if empty in "style":
The original form is like below;
StyleGarmentFabric1001
BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40Recon Sports Bag600D polyester
What i need is like below:
Code
Style
Garment
Fabric
1001BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40
Recon Sports Bag600D polyester... ...... ...
step by step:
stpe 1: move numbers out to a new "Code" colum;
step 2: delete the row if empty in "Style"
I need a function that can find a single digit in the cell containing the shifts worked. As an example, the cell may contain 1, 2, 3, 12, 13 or 23. I have tried using the FIND function, but so far I have not been able to make it work. I will continue to research the FIND function, but a function that would search a cell "containing" a specific digit would be ideal. If there is such a function out there.
View 9 Replies View RelatedI am trying to use a macro to copy a set of cells.
I have successfuly created a macro - the problem is I would like the column reference to be absolute but the row reference to be relative. I can have one or the other but not mixed it seems.
If I have not explained myself well, perhaps this may help - I need to copy cells $J1:$S1 and paste them to $L1:$U1
Currently the macros works if the cursor is in cell J1 when I activate the macro but if I move the cursor to any other cell it copies and pastes the wrong set of cells.
I'm trying to formulate:
If it's mixed case, copy it. If it's all lower case or ALL CAPS, then make it PROPER.
i.e., if 'bananas' is in A1 and B2 says =PROPER(A2), then B2 says Bananas, because PROPER capitalises the first letter of each word.
I would like to do
[Code] .....
I have a worksheet with a row of mixed cells near the top. By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).
GOAL: I want to sort the columns to the following order:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.
So far all I have been able to accomplish is:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.
OR
First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.
This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.
I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.
Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.
I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.
I pulled a report into excel that lists staff details and workgroups that they have access to. There are nearly 8000 staff who can have anywhere from 0 to 120 workgroups.
The workgroups are listed with the staff details in the following format:
SurnameForenamePost Title183860314040|188778743040|261226948048|584865373040|088365861041
SurnameForenamePost Title695416612049|751836367043|430463930049|461208099048|488798547045|723225723045|183860314040|472108996043
SurnameForenamePost Title177448555041|315136549047|400323457046
With the whole list appearing in one cell and not in numerical order.
I need to pull out a list for each workgroup, with all the staff who have access.
Even if there was a way I could separate out the workgroups and get them to line up.
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in
D4. So,formula needs to be in (D4, E4, F4) there are other datas in the worksheet
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...]
it repeates the same fomula as D4 and does not adjust for the increase in column number.
I have data stored in mixed cell that i need to extract to different cells.
Q/K code: AZVR Name: "AzVR" Ltd Nominal: 0.1 USD
R. Number: AZ3570011 Category: ABC
So i need each of AZVR, "AZVR" Ltd, 0.1, AZ3570011 and ABC in different cells. The characters could be in different lengths. Is there any formula that can find Q/K code in text then write characters after it until space or Name starts?
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
Is it possible to have a caption on a label with mixed font size. I tried this but the 2nd call just overwrote the first.
Code:
Private Sub UserForm_Initialize()
Call EM
Call aint
End Sub
Public Sub EM()
[Code] ......