how do i separate information like this:-
01/08/2007,09:54:27,27.05,27.06,27.05,361300
let say its in cell A1 & i want first information before the First , to appear in B1 which is 01/08/2007
then the information before the second , to appear in C1 which is 09:54:27
and so on
How do I Extract information before a sign "/" in a separate cell ?
View 2 Replies View Relatedi succeded to read a string of data from a textfile
String_Data= "EURUSD,20010103,000200,0.9506,0.9506,0.9505,0.9505,4"
Now i want to separate it so each informations goes to it respective variable
Market_= "EURUSD"
Date_= "20010103"
Hour_= "000200"
...
...
...
...
Volume_="4"
How can i separate the string information in individual variables using a macro.
I am trying to separate the data in my spreadsheet. I will attach the spreadsheets and explain below. T
The column labeled number has entries that include commas. I need each number entered separated and put into its own row and I need the remaining data from the original row duplicated to the new rows.
Here is an example using the first entry.
Original entry
November Wiseman 1,2,11 Bluen Medical CA
How it needs to be separated
November Wiseman 1 Bluen Medical CA
November Wiseman 2 Bluen Medical CA
November Wiseman 11 Bluen Medical CA
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View Relatedi have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedI have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
I have a Formula question which I can't seem to figure out. I want to set a cell so that when two other cells are above 80 then the cell is green.
If one of the two cells is bellow 80, the cell turns to yellow and if both are bellow 80 then the cell is yellow.
I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.
The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.
I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.
So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.
Here's a picture of my current spreadsheet : [URL] .....
Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.
I have a cell that has data like the following: ab 4111 / nw 2652 / ev 8741 up to 5 or 6 entries in the cell. If I just want 1 of these entries say the ev 8741 how can I have that be the only data that remains in the cell?
View 9 Replies View RelatedI'm trying to build a macro that copies the information from cell D2 and then special pastes it into the next free cell on that specific row (as the information is from a vlookup so don't want to paste the formula) - ideally I'd like it to paste from H2 onwards but can move the spreadsheet around if that's not possible! I'd then like to do this for every row until there is no data in a row.
I've been using the below which I've found on the internet but this only pastes into column H and then just goes down the column rather than across the row, and I can't figure out how to do this.
Sub Summarize()
Range("D2:D25").Select
Selection.Copy
Sheets("Pot 2").Select
lMaxRows = Cells(Rows.Count, "H").End(xlUp).Row
Range("H" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D25").Select
End Sub
In my spreadsheet, certain transactions are labeled as "Fees" in Row G. I need any amounts in Row D to be negative if they are labeled as "Fees" in Row G.
I could simply find them manually and make them negative, but the logic needed in this question will help me with similar problems I am facing.
I am thinking it is probably some combination of the "IF" command.
I have spread sheet with 2 tabs (MASTER LIST & CYCLE COUNTS), on each tab I have 2 columns(LOCATION1 & DATE1 and LOCATION2 & DATE2) I need to verify if I any cell from LOCATION1 is repetead on LOCATION2 and copy the informacion from DATE2 to DATE1. then if I update the CYCLE COUNTS sheet I want the MASTER LIST update by it self.
This is the formula that Im using but Im having problems with the range..... =if('Master list'!$A2$:$A$15000='Cycle counts'!$A2:$A$15000, 'Cycle counts'$B&2:&B&15000,0)
I have this information available.
test.png
And what I want to do is use this information further down in my excel-sheet. So whenever I plot in "Location 1" I want "Security" and "John Johnson" to magically come automatically.
Like this : test.png
I have a string that has an IP that doesn't follow a fixed length.
Eg:
10.248.242.65Microsoft SQL Server Compact 3.5 Service
10.248.242.5EOL/Obsolete Software: Oracle Java SE/JRE/JDK 6/1.6
10.248.251.144Microsoft Sync Framework Service Pack
seperate that IP and the String. I tried the below formula =REPLACE(A2,MIN((FIND({"M","E","S"},A2))),0,"?")
It works fine for the above examples, but i want a formula that can seperate the string after the IP ranging from A-Z or a-z
getting any formula so that i can determine atleast the position of the First Letter(A-Z,a-z) from left.
I have a listing of numbers in different catagories such as the following:
Type A:
1
Type B:
2
Type C:
4
Total:
7
I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"
Type B:
"(2/7)"
Type C:
"(4/7)"
with that exact formating.
I have several thousand cells where I need to pull out some information at the end of each cell. So for the examples listed below, all the information resides in the "A" column. For example:
M55116/14-5 CONN,RECEPT,6 PIN,U-228 TYPE B EA A AS 17 85.00 1,445.00
M39029/30-222 CONTACT,#0,RED-RED-RED B EA A AS 96-0327 46 6.75 310.50
M39029/44-288 CONTACT B EA A AS 93-0852 400 2.18 872.00
I am trying to pull off (starting from the end) all of the information to the first space. So I would like the following results returned: 1,445.00, 310.50, and 872.00. Can anyone recommend if there is a Function or equation that I could use that would pull everything from the end of the cell until the first space?
I have a dataset with full addreses listed in one cell. I would like to use "text to columns" to separate the data but am unable to use the text separater in the wizard. When I open the text to columns wizard the data appears to be divided with a "square" but I cannot add it as "other" for the delimeter. see the attached.
View 2 Replies View RelatedI have a cell with several values; however, Excel seems to only "see" the first item. I have tried to use Text to Columns with no success. I have tried copying and pasting the cell in a variety of formats with still no luck. I think a macro may work, but I am not skilled enough to know what to do. Here is an example of the cell data:
"PRT-BNS-SVR-01/p;2
PRT-BNS-EOC-01-5.5.04/-;1
PRT-BNS-SRD-01/t;4
PRT-BNS-SDD-03/f;1
PRT-BNS-SRD-03/g;1
PRT-BNS-SCI-01/bn;1
PRT-BNS-SAS-01/t;1
PRT-BNS-SDD-01/f;1
PRT-BNS-CIA-01/p;2
PRT-BNS-SVCP-01/r;2"
In this case, the first item is PRT-BNS-SVR-01/p;2. I need separations between the / the ; and between each item. I am not sure where the quotes come from, as this only happens when I copy and paste to text. I can not see them in Excel. When I use Text to Columns, the first material will separate, but it's like the others so not exist.
I need a VBA code to take everything after a certain word and put it in a new cell below it. The cell would have View More in it most of the time, and sometimes spaces will be messed up, so it will have a name after it but it should actually be in the cell under it. So if the name is A Maple, it will say View MoreA Maple. So the A Maple need to go in a cell below it.
View 6 Replies View RelatedI receive a workbook on a monthly basis and it arrives in csv format. I need to format it and split it into several pages prior to sending to managers. I'm comfortable with the vba for formatting and splitting it into several pages but I have a problem with some cells that always have data in them in the same (incorrect) fashion.
how to go about fixing these cells and then I can get on with writing that into the macro.
Currently looks like:
A || B || C
No requested || No Supplied || Percentage
4 || 4100%
12 || 875%
6 || 350%
Should look like:
A || B || C
No requested || No Supplied || Percentage
4 || 4 || 100%
12 || 8 || 75%
6 || 3 || 50%
I was thinking of some kind of Left() formula to extract the leftmost character and place it into it's new cell BUT realise that sometimes I might need to extract 2 (or perhaps even more) characters.
I'm trying to extract information from one specific cell, and place it into another. This other thread kind of does what I'm looking for. I wish to extract from the following; "20/04/2009 5:42:51 AM". Placing the date in one cell, and the time in another. All spacing within the quotation marks is respective of the cell. Obviously dates and times would vary.
View 4 Replies View RelatedI have a table in the format:
MR A MR B MR C MR D
01/01/06 1 3 7 8
02/01/06 2 5 4 4
03/01/06 3 9 5 7
I want a formula to analyse the entire table (Which can change) and to
return me who has achieved the Max value and on which date.
The answer here should be MR B on the 03/01/06.