to extract a number from an imported cell containing text, a number and date. i need to use this number as part of a formula in another cell and the data needs to be refreshed every hour.
eg. the cell imported is "USD 30.97 (March 27, 2007)" and i need to use 30.97 in another cell.
i've tried using data - text to columns, but i realised that after splitting the cells, the 2nd and 3rd columns can't be refreshed.
I need to extract part of the data from a number of cells. I was thinking a vlookup would be the right way to go about this but i don't know how i can make it look for only part of the data in a cell.
example data Required Extraction Data
PXC-WIS-HAR075/001 = I need the data HAR075/001
GXR-Cru-WOO046/001 = I need the data WOO046/001
LST-Wis-KAV001/001 = I need the data KAV001/001
GXR-CAP-EDL001/001 = I need the data EDL001/001
RXW-ACT-WAL081/001 = I need the data WAL081/001
SXS/mf/SMI085/001 = I need the data SMI085/001
SYM-CEN-HAP001/001 = I need the data HAP001/001
SYM-Act-MAY025/001 = I need the data MAY025/001
I should have also wrote that I do have a list of the file numbers for the vlookup to.
Extracting part of data from cell .....
View 9 Replies View RelatedIf 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"
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 column of cells with similar to the following text.
First Marine Avenue 18 1303 11 1
There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)
The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)
I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have a workbook that requires refreshed source data each day. The workbook has all of the macros and formulas that analyze the data. I have the following code to import the worksheet with the raw data (onto a fresh worksheet in the calculation workbook), but I would like to create code that also adds the date and time to the imported data worksheet -- not the date/time the raw data was created; instead, when it was imported into my calculation workbook.
Below is my code for importing the raw data worksheet:
[Code] .....
I am working with Microsoft Excel 2003. I am trying to import(or something
like it) from worksheet (A) to worksheet (B). Worksheet A is a spreadsheet
that I have saved to keep the same row names, etc. but the information within
the named cells is forever changing. I also save the information from
worksheet A, but have another copy that when I open, it always opens without
any changed data in it. MY question/problem is that I need to import the
data from A to B, and every time that A changes, I need B to automatically
update the data and continuously add to the spreadsheet I have made up for B.
I can NOT have A overwrite any data that I have already put into B.
I've imported a few thousand lines of data into excel, but some rows have a rogue character or a blank cell in the middle of them taking up a cell and shifting the rest of the row along one cell and putting it out of sync with the columns I want that data in.
Is there a way of remedying this without going through and manually moving a couple of hundred rows along one cell.
I'm not sure I've explained that particularly well, so I'll try and illustrate it with an example.
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB ------ 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
Ignore the fact that they are all 1.23, I've just used that to make it quicker for me to show. As you can see row 3 has a ------ (representing a rogue character or blank cell) in the 5th column shifting columns 5-9 right one cell.
Now as I said I have a few thousand lines of data like this with maybe 2-3 hundred rows with this problem and I really don't want to manually go through and fix each one.
Is there a tool in excel or a way of using VB to fix this?
Alternatively, is there a way when importing the data to have a number of delimiters rather than the one extra one you can select to remove these on importing?
Maybe it could be fixed by taking any blank cell (or character) and deleting it whilst shifting all cells to the right of it one cell to the left?
I have import the database from SQL server, all the records imported begins with a blank space and they can not be trimmed using Trim function. Is there any smart solution ? I prefer to get VBA code to solve the problem.
View 9 Replies View RelatedUsing VBA to clean up imported data ...
View 9 Replies View RelatedI have recently changed my software at my work from Sim-pro to Clik. I have about 4000 customer details that have been exported from Sim-pro to a CSV file but unfortunately some of the addresses are in one cell and need to be split up into 2 or 3 cell so it can be imported into Clik. In the Sim-pro program addresses are entered into one dialogue box e.g Address 1(house name - if applicable), Address 2(street name) & Address 3(suburb/area), after each line you press enter to separate them. The town/city, county and postcode do have their own entry box though and these do import normally into excel.
Unfortunately to import the addresses from the CSV file into Clik the Address parts 1,2 & 3 need to be in their own cells but obviously these are all in one cell(not every address have 3 parts some just use 1 address line which is fine). Can excel recognise the imported information from Sim-pro that is separated by 'enter' in that one cell and move them into another cell? I have tried using text to columns and selecting delimited and entering 'ALT 010' in the 'other' box but that doesn't work.I'm dreading the thought of going through 4000 addresses and cutting and pasting parts of the cells....
I imported data from another program to Excel, it is an address database. Some of the entries are all capital, some are all lowercase, some are proper. I would like to switch everything to proper. I have tried numberous times to do EXACTLY what I am reading about copy and pasting, entering the formula, designating the cells I want it to apply to. However, when I do it, nothing changes.
View 9 Replies View RelatedI have exported a aging report from SAL to excel. Now the problem is each figures in the report is not in number format. when i checked each cell contains a space after the numbers so excel does not treat them as a number format.
how i can remove all the spaces in those cells. find and replace doeasnt work.
I have imported some files in to excel. I have attached the test document to give you an example of exactly what Im working with. Here is what I need to do.
1) On a separate worksheet I need to sort out the data by invoice number (Column E).
In the example I have on the worksheet titled "Main" I have listed what I would like it to look like. The purpose of this is that I don't want any spaces in rows. I just want a long list of invoice numbers and their information (Including the project number & customer on all rows). In long story short I want to erase all the information from A18:A25, and add project number and customer name to all the correlated invoice numbers. The way I think I can do this is that the first 4 digits of the invoice numbers are the same if their in the same project.
i want to import data from sheet2 a1 from sheet1 a1 continously. now problem is when iam importing i shouldnt loose my previous data. eg: at time 11 when i import data to sheet2 a1 from sheet1 a1 with value 9. and at time 12 when i import data to sheet2 a1 from sheet1 a1 with value 10. value 10 will be over wrting value 9. here i shouldnt loose value 9. i want values comming afterwards should come to next row.
View 3 Replies View RelatedI have an excel spreadsheet that contains customer data exported from Quickbooks. In that file there is a column called CType (Column G) which contains the scheduling cycle for that customer. There are eleven different possible schedules, including 15xYr, 1xMo, 2xMo, EOM (every other month), EOW (every other week), EOW-S / 1xMo-W (every other week between 5/1 and 10/31 and once a month during the rest of the year), EOW-Th (every other week but must be on a Thursday), ETW (every third week), On Call, W, Q.
Although the number of records changes daily, on average there are about 950 rows of data in the spreadsheet.
I need to set up an array (?) for the values in column G starting with G2 through the last non-blank row (all records will have data in column G), and calculate the next scheduled service date based on the schedule type shown in column G and the last service date which will appear in column M of that row. The last thing I need to consider is whether there is a value in Column N, which would represent a hard-entered ‘Next Scheduled Service Date’. If I find a date in that column it needs to over-ride the calculated value. My assumption to this point is that I should use a new column T to hold the value of the calculated schedule date. There are a few other details, but rather than confuse this whole thing further I’ll stop there.
What I’m struggling with is the most efficient means of running this macro. I’ve been looking at many, many threads over the past few weeks, reading
VBA Programming for Dummies (which I apparently am), and reviewing other resources.
I want to be able to do, however. I want to make a spreadsheet that has all the equipment items preloaded into it, along with the quantity that we're supposed to have of said equipment. Then, I would like to import the data from the text file, having it match items in the spreadsheet to their equivalent in the imported text file, and then bring in the quantity scanned. That way, I can use some conditional formatting to show when there is a miss match between "Required" and "On-Hand" quantities.
What I don't know how to do, is get Excel to only import the name of the item, and the quantity scanned, instead of all of the information that the scanner spits out. Also, I don't know how to make it match the name, with the one preloaded into the spreadsheet, so that the correct quantities are matched up. Here's an example of what the scanner spits out: FIELD WIRE,6145-01-155-4256,[Scanned Quantity Goes Here],N/A,N/A,RESPONSE TRAILER,,
The scanner creates records like this the first time you scan an item. So if you scan Field Wire first, it's the first record. However, if you scan it fourth, it's the fourth record. How do I make that match up with a predesigned spreadsheet?
Have the following code:
With iSheet
i = .Range("A" & Rows.Count).End(xlUp).Row
j = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range("A1", .Cells(i, j)).ClearContents
[Code] .......
If I use F9 to pause the code before the sort part in blue, then the data imports into Excel from the database and then subsequently pressing F5 sorts the data as required.
However, if I run the macro in a single pass, the data does not sort. Why it's not sorting as expected and how to make it sort as required?
I am importing data from my AS400 in Excel, the dates are coming into the spreadsheet as numerics. How do I convert these numbers into a date format? i.e. 3202002 should be 3/20/2002, how do I get the number to that format?
View 9 Replies View RelatedWe have a software program that we use to takeoff HVAC equipment on drawings. It will export all of the items to an Excel worksheet. Once it is into Excel I need to sort the data by two or more different columns, then sum three to four columns for each unique item for transfer to the equipment sheet for pricing. I've searched this site and others, but have not found a way to do this with VBA in Excel.
Floor
Rank
Name
Description
Qty
Cost
Labor
Seismic
[Code]....
The sheet is a simplistic view of what I'm trying to explain, obviously in high rise there will be many floors and hundreds of items. The actual spreadsheet has approx 12 columns, but only 3 or 4 need to be summed per similar item. Most times I sort by Range & Name. On occassionwe need to sort by Floor, Range & Name. Used to do this with a Database & RR Report Writer, but the new software does it onscreen with digitized drawings.
I've created a spreadsheet which, on running a macro, imports data from a text file, formats it and then sums various parts of it giving me a subset of the large file as a range of data for a chart (a pie chart) on Sheet 2.
The problem begins when I delete the data in the worksheet in preparation for another import - the Pie chart just loses all the data and I have to manually reselect it in the chart each time.
Once deleted the chart is blank, but as soon as the data is imported, I get reference errors when I click on Sheet 2
I must be doing something wrong. You can delete data and repopulate it and the chart should pick up with the new data. If the cells it is referencing are empty, it is blank. Not so here.
Is it to do with importing the data? Or the formula for my chart data?
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
I have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).
View 12 Replies View RelatedI have a google earth KML file that I have converted to text, and through a bunch of manipulation have been able to pull a series of GPS coordinate strings into a single cell string. Unfortunately, the string data is beyond what excel 2007 can handle for a single cell. so my thought is to have excel pull each coordinate string into a separate cell with which i can then run a macro to develop a new KML dynamically. (changing multiple attributes based on a query to a database)
Each string of coords actually maps out a single region (path) on the KML, truth be told it is telecom rate center data, and each rate center will have numerous other variables applied to it depending on my company's voice network capability for a given rate center. Currently my only desire is to depict differently any rate centers that I'm able to deliver VOIP services to by showing them in a different color...but these change very often so it will support to be able to auto generate the map from time to time.
the raw data from the KML looks like this:
Code:
CLINTONVL
RATE_CNTRCLINTONVL
STATEPA]]>
ff000000
1
0
1
[Code] ..........
After doing my data import, i extracted via various manipulations, the rate center name (a common lookup value that stays constant across multiple databases), and the string of coordinates. this is where i run into trouble. i need to pull each coord into a separate cell assuming i won't run out of cells in the x coordinates to gather this data OR find a way to grab the data via another lookup to another document...not desireable.
I want my output to look like:
Code:
RC_NAMECOORDSLATAVoip?
ALIQUIPPA
80.334114,40.520974,0-80.327493,40.520023,0-80.323915,40.51957,0-80.31997800000001,
40.519933,0-80.316958,40.521221,0-80.314431,40.521445,0-80.314262,40.521332,0-80.31398,
40.521194,0-80.31315600000001,40.520832,0-80.313057,40.520794,0-80.312805,40.520641,
[Code] ........
First off, my import was jacked up by missing some comma's...this i can fix easily with the string importation and manipulation HOWEVER...i still run into the issue of string length OFTEN.
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
I have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.
View 4 Replies View RelatedWhat I am trying to do is automatically move the date received and the total qty received from the Rcvg. Log sheet to the RFMs Release sheet.
The problem is:
1) I don't know what I'm doing
2) On the Rcvg. Log several part numbers that are the same with different Qtys.
3) on the Rcvg. Log the are several part numbers that are the same using different programs.
What I would like to do is combine all the same part numbers with the same program add the total Qty received and paste the date received and total Qty into the RFMs Release sheet in the proper part numbers row.
Is this possible?
Attached is a shortened copy of the spreadsheet normally there about 3000 entries.
Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).
This is what I'm looking at:
A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")
In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.
