Query On Text To Columns

Jul 11, 2014

Attached is the sample working file, i have a user data embedded into the comments columns for each user reference. However, i need to extract the wrapped comments column.

Text-to-columns-sample.xlsx‎

View 14 Replies


ADVERTISEMENT

2 Columns, 5 Averages Of Only Negative Numbers Query

May 2, 2007

I have 2 columns of data, one the description, the other the number, both positive & negative numbers, (the columns are also not next to each other). I need to average only the negative numbers according to the description.

I can sum only the negative numbers if I separate out the different descriptions, but cannot average the figure:
=SUMIF(E2:E300,"

View 9 Replies View Related

Add Date Columns From Mysql Select Query

May 9, 2007

I have a workbook that is connected to a MySQL DB.

I want to get all the months organised in columns then the Amounts of those months underneath the specific month. I can only do one at the moment in this select query but do not know how to go forward by creating the other month and their data?

My code looks like this:

Private Sub cmdGO_Click()

Dim conn As ADODB.Connection
Dim dbRecset As ADODB.Recordset
Dim sSQL As String
Dim l As Long, l2 As Long
Dim Last_Row As Long

View 7 Replies View Related

Matrix Query Using Text?

Mar 18, 2014

with the table give below, i need a formula to get the sustainability. Eg. cell D1 =VG and D2=G, then D3 value should be G (as per the table below).

Prev Yr
Current Yr
Sustainability

EX
EX
EX

VG
G
G

G
EX
VG

View 2 Replies View Related

Web Query: Compare Columns & Date Time Stamp Differences

Oct 10, 2007

I have two identical web query's on the same sheet. One from column A to column I and the other one from column K to column S, and both have 404 rows. The one on the left (from column A to I) autorefreshes every 60 minutes and the one on the right (from column K to S) autorefreshes every 4 minutes. I want to subtract the numbers in column Q from column G and the ones in column R from column H, and in both cases if the result is bigger than 0 to place a timestamp in column Y(for Q-G), and in column Z (for R-H). I tried with NOW formula, but the timestamp changed every time the web query from the right autorefreshed, no matter of the result. I guess that I have to use VBA codes...but I'm not good at that .

View 9 Replies View Related

Find Text String In Web Query

Feb 18, 2010

I want to find the number within this webpage and report it to a Cell in excel....

View 6 Replies View Related

Web Query Returning Text But Not Numbers

Oct 3, 2009

I have this web query to retrieve Stock Market data for individual scrips which used to work fine till 2 weeks ago:
[url]

Since then all it returns is the text in the tables. Numbers eg dates and stock prices come as blanks.

These show up alright in the browser (Mozilla as well IE).

Some other tables at this site plugged into query return complete data. eg [url]

I tried many different scrips with same results.

Since it was working fine till 2 weeks ago AND other tables at this site are working fine, I suppose the site changed something.

View 10 Replies View Related

Running Query To Access: Pulls Data From 2 Columns In A Table And Pastes It To The Spreadsheet

Jul 10, 2009

I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.

View 2 Replies View Related

Open Large Text File With Query?

Sep 20, 2006

I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.

The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).

View 5 Replies View Related

MSN Money Web Query Has Numbers In Text Format

Aug 28, 2009

I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.

If I use:

View 3 Replies View Related

Converting Number To Text From Refreshed Query

Jan 29, 2009

I am importing data from an access database that is continually refreshed. Some of the data that is returned to my Excel sheet is in binary, where a "0" represents an unchecked box on the database program and a "1" represents the same box, but with a check mark. I want to change the data so that when it is updated on my Excel sheet it is converted to text as either "Yes" or "No" instead of "1" or "0", respectively. I'm sure this is pretty elementary stuff but I have tried VBE and cell formulas and can't come up with an answer.

View 3 Replies View Related

Conditional Formatting Query: Changes Colour When Certain Text Is Entered

Sep 10, 2009

I have a column of cells that changes colour when certain text is entered into them, ie "TIME" will make the fill colour brown. However, when other text is entered into the cell alongside "TIME", the conditional formatting doesn't change the colour of the cell. How do i get around this so it picks up on the "TIME" text even when other text exists alongside it?

View 2 Replies View Related

Export Excel Query To Multiple Text Files

Apr 2, 2012

I need to create a function which will take a objectname (a query or table) and a file name format

The objective is to export this query or table into multiple text files each with a maximum of 1000 lines per text file. These files are used as an input for SAP - the interface only takes a maximum of 1000 lines per file.

I am having three issues with the code below.

1. How do I split an excel query based on line numbers? Is there a way to get the equivalent of ROW_NUM in oracle ? Or do I have to loop through it and maintain a counter?
2. What is the best way to split the file ? Loop within loop? I need the files to be tab separated (no header required)
3.The rst.RecordCount is acting strange - when I pass a name of the query - the property returns the right no. of rows, however when I pass a any table name it only returns 1 - is this the expected behavior - or am I missing something

Code:
Function ExportAsText(strObjectName As String, strFileName As String) As Long
' Purpose: Export any given query, table to mutliple files each of certain length
' the no. of lines per text file is defined by the config parameter SAP UPLOAD, LINE LIMIT PER TEXT FILE
' in the LKUP_CONFIG database

Dim db As DAO.Database 'This database

[Code] ..........

View 1 Replies View Related

Macro To Query Column For Date If Found Place Text In Cell

Aug 30, 2012

I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).

The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.

The Federal Holiday schedule is in column range J (Date) and K (Holiday)

This is where I am stuck ....

EmployeeTimeSheet_2012.xlsm

View 2 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View 9 Replies View Related

Text File Import With Text To Columns Splitting Same Text Differently

Nov 21, 2007

I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View 6 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View 2 Replies View Related

Excel 2013 :: Set One Columns Text To Color Based On Another Columns Results?

Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies View Related

Text To Columns/time Formatting Split Them Into Columns

Jan 5, 2010

I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

View 2 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Match Numeric Data With Query Text Data

Oct 2, 2007

I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.

I lookup and employee number on my sheet to the employee number in the database table.

this is my code that checks the employee number.

Set rs = Worksheets("Employee")
If rs. Cells(r, 2) = cells(1,1) Then 'if employee number matches
......

The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.

Example if the number was 127 I need to enter '127 in the cells.

I have a few thousand in my data.

Can I add some code to my macro so I can still enter just the number in the cells?

View 4 Replies View Related

How To Split Text From Text String Into Separate Columns - No Delimiters

Apr 8, 2014

I have the cell data as below

How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?

I still want the original data as I need to check that the splits worked well?

16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5

View 2 Replies View Related

Extracting Strings Of Text From Columns Of Data(text)

Apr 23, 2007

What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.

As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

View 9 Replies View Related

To Populate Three Columns With Text Based On Text Of Another Column

Dec 16, 2009

I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.

For example

Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"

I am using MS excel 2007.

View 9 Replies View Related

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Text To Columns :: Macro To Split Text

Jun 17, 2008

I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in).

The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.

I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.

View 11 Replies View Related

Use Text To Columns Feature To Be Text To Rows?

Jan 13, 2003

I have a cell that has a comma separated value that is 354 fields long. As such, if I use the Text To Columns feature to split the data at each column, I lose several columns (because excel cannot have that many columns).

How can I break the data at the comma, but have it list in rows instead?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved