Trying To Avoid Quote And Comma Stripping In Formulas
Apr 18, 2006
I have to create a formated file to test our system every other week, I want to create a File Generator using Excel and the replacement values forumla "& A1 &". However the file is in a CSV format containing a lot of quotes and when I attempt to use a line of the file in a formula with "& < Cell> &" excel reports errors, if you accept excels help it strips out the quotes and commas. Anyway around this? having to have a user save the file is CSV format would be to complicated. Sample file attached.
View 2 Replies
ADVERTISEMENT
Sep 2, 2007
I have this formula:
=IF( COUNTIF($H$42:$H$76,"A~*")/COUNT($J$42:$J$76)="","",COUNTIF($H$42:$H$76,"A~*")/COUNT($J$42:$J$76))
which seems to work fine if there's data in the range to compute. If there isn't it throws a DIV/0 error at me. How can I modify the above to
Be blank if there's no data to display in the range, therefore avoiding a div/0 error.
View 3 Replies
View Related
Mar 30, 2013
Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .
Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).
Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).
E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
View 7 Replies
View Related
Jan 9, 2008
I have a list of names that I need to separate into FIRST and LAST names. These names are located in column A. The problem with my initial list is that some of the names have two first names (in the format of Herman & Betty Altenfelder) and some just one first name (such as Tom Frank). Could someone please help me in writing a function that will place the first name(s) in one column (such as column B) and the last name in a different column (such as column C).
View 9 Replies
View Related
Jan 22, 2007
I have a range that can have numbers and text in it. I want to strip out anything before and after the number. For example. If any of the cells contain text such as "The price is $4.28 per pound" it would strip out everything except the 4.28. This cell may also contain Japanese yen so you can not use the decimal for reference.
View 14 Replies
View Related
Mar 18, 2013
I have a cell (A4) on my worksheet that displays 64421 but has a hyperlink associated with it.
I need to use the value in A4 in a vlookup formula, however, I am getting NA# errors when I use it.
Code:
=vlookup(a4,wshgroup("A1:Z500"),4,false)
I suspect that as a hyperlinked value, it does not actually hold the value 64421 needed to match in the vlookup range.
Any way to ensure that the value for A4 is consistent with the numeric data in the lookup range?
View 3 Replies
View Related
Feb 8, 2007
I have a text file that has been saved via PL/ SQL code from a CLOB (due to the MSDAORA not supporting CLOBs). Unfortunately, due to passing the file between operating systems it appears that the CR and LF characters are not consistent. This means that my third party application in Excel cannot read the text file properly.
View 2 Replies
View Related
Jan 31, 2014
I have a macro that needs to work with data including different time from different sources.some of the cells are date and some are text.
I have the following function
Public Function TidyDate(zdate) ' This is coming in as a date #01/02/03 08:00:00#
Dim TempVar1, Tempvar2 As String
TempVar1 = zdate
Tempvar2 = Left(TempVar1, 11) & "00:00:01"
TempVar1 = Tempvar2
[Code] ....
What i need is to change the time of the variable to 1minute past midnight but keep the date the same.
View 5 Replies
View Related
Sep 18, 2009
Here is a simple exercise that is necessary only because of the large amounts of data involved.
I have a spreadsheet of columns with budget and actual data. I need to strip out the budget columns (marked as budget) and total column. then add back in at a later date.
It all has to stay in the same spreadsheet.
I know it sounds strange but believe me its necessary.
I was thinking of moving them to the right side of the spreadsheet. Then back at the later step.
I have attached a simple spreadsheet showing the data on sheet1 and how it should look when stripped on sheet 2. and the later step would move them back to sheet3(which is the same as sheet 1).
View 14 Replies
View Related
Aug 29, 2006
I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.
The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)
To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-
View 5 Replies
View Related
Apr 11, 2006
I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:
123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One
I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1)))))
for stripping the text before an actual number
Acme Company, Inc. 456 Easy Street
to
456 Easy Street
BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)
Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One
View 10 Replies
View Related
Mar 19, 2009
What I require is the macro code to achieve the following:-
Cells A2, B2 and C2 will be completed manually, however, when a new quotation is required the user must enter a new Control Reference Number in cell E2 and then press the Generate button.
Once pressed the Macro behind the button will generate the following :-
(a) Quote number underneath the last one in column 'A'
(b) Change Ref underneath the last one in column 'B' (this was the ref number input above in E2)
(c) today’s date underneath the last one in column 'C'
(d) a blank Quote Tab i.e. labelled the next number in the sequence i.e. Quote 2, Quote 3 etc which should be created copying the Template Tab
N.B. the total number of Quotes in any one work book should be a maximum of 30 so some form of checking would be required.
View 9 Replies
View Related
May 24, 2012
I need to calculate a quote over 4 years but the first and last years are not full 12 months.
On Row 20 are monthly costs for each year. The start and end date is in Row 3.
I need to find a formula that can calculate ANNUAL TOTAL for the all these year in Row 22 populated automatically, not just manually times the months in each cell.
Here is a sample file.
View 9 Replies
View Related
Sep 18, 2012
I have been using Excel for many years but haven't dived much into using VBA. I believe it holds the answer to my question. I have developed a spreadsheet with 10 tabs that is used for billing customers. I'd like to take it a step further and, on a separate tab, have a quote automatically populated and then be able to be printed. I need for the VBA to scan certain areas in each tab to see if data is present or greater that 1. If there is data present, it would need to list the content of 3 cells (description, quantity, and total cost). I know that I can directly link but it needs to be dynamic and only display/show data that is present (as not all cells will be needed for every client).
View 2 Replies
View Related
Dec 12, 2006
Someone might find this interesting. Inherited a file that need to total columns but almost 50% of the file has cells w/ the single quote tick infront of the number.
Ex '2257894 or '2234. How can one remove those single quotes out of a cell? NOTE: Cell values are not the same length.
View 9 Replies
View Related
Jul 29, 2009
I work in sales and when under target I need to know how much more i need to sell to hit target. e.g
quotes 10 sold 1 currently at 10%
my target is 50%
at the minute i would have to work this out manually
like i need to quote and sell the next 8 to hit the 50% target.
What can i use on excel putting my current quotes and sales and my target in so it gives me the quotes and sales i need to do?
View 9 Replies
View Related
Mar 19, 2014
Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
View 3 Replies
View Related
Sep 5, 2013
Any escape characters in Excel. I'm trying to do something like this:
=FIND(""",A1)
View 5 Replies
View Related
Sep 7, 2013
Way to create order and quote numbers. I'm currently starting an order number with a "O" in front of the order number and a "Q" in front of the quote number but this presents a problem when trying to create the next consecutive number because the number has a letter at the start of it so I can't use the method plus 1. I'd just simply remove the "O" and "Q" from these numbers but I need the numbers to be unique from each other and putting the "O" or "Q" in front was the only thing I could come up with to avoid the numbers ever being identical. I often need to look up these numbers and return the values in that row so the numbers need to be unique from one another.
The information for quotes and orders is saved on separate workbooks but I'd still like the numbers to be unique from one another.
Having a letter at the start of each number or a work around for using the plus 1 method when you do have a letter at the start of the number?
View 9 Replies
View Related
Feb 20, 2008
i want to create a quote worksheet with a type of material validated list, a type of stock validated list, a volume of material sum, a cost per lb of material varible and a typ of stock % markup to quote stock material. i have a pdf of what i want it to look like i just need help formulating it. thanks. Auto Merged Post;here is a link to the pdf. i am not sure what to call what i want to do...
[url] Auto Merged Post;here is the xls file.
View 8 Replies
View Related
Jul 12, 2012
I am trying to create a basic stock quote reader.
I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.
For instance, I I were to replace
"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1"))
with
"URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))
It would then work, but I would have to manually change it every time.
Here is the code
___________________________________________________________
Sub Macro1()
Dim TICKER As String
InputBox ("enter ticker symbol")
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
[code]...
View 1 Replies
View Related
Feb 19, 2013
Is there a way to clean all of the single leading quote marks from all cells in a sheet?
'Bob
'Tom
'Jerry
to
Bob
Tom
Jerry
I'm importing data and I have several columns to deal with and will never know how many columns/rows are in a sheet.
View 9 Replies
View Related
Jun 23, 2014
I want to alert a user that he or she has typed in a text string that begins or ends with an apostrophe ' or begins or ends with a quotation mark " so that they can take corrective action in removing these characters. The reasons for this are superflous to this post but what I thought was a logical answer returns a formula error. Here's the scenario:-
A user enters a string of text in cell A1.
Cell B1 checks if the text, if any, in A1 begins or ends with a ' or a " and if it does, returns an "Error" message
The formula I've tried in B1 is =IF(OR(LEFT(A1,1)="'",LEFT(A1,1)=""",RIGHT(A1,1)="'",RIGHT(A1,1)=""")),"Error","Ok") but Excel will have none of it.
View 9 Replies
View Related
Jan 27, 2014
I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.
I would like the formulas to start in column B-D (data in A)
I am looking at formulas based on specific instances of a quote mark in cell from data in column A
Formula 1 - Return with characters up to and including the 5th instance of """
Formula 2 - Return with characters after 5th """ and up to and including 6th """
Formula 3 - Return with characters after 6th """ and up to and including 7th """
Formula 4 - Return with characters after the 7th instance of """
Example below...
Cell A1
xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"
Desired results
Cell B1 - Formula 1
xxyy","aabb","ee,ff,"
Cell C1 - Formula 2
"gghh"
Cell D1 - Formula 3
hh,"
Cell E1 - Formula 4
mmnbijp"oossww"
View 6 Replies
View Related
Oct 9, 2009
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")
Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).
I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.
View 4 Replies
View Related
Oct 10, 2006
Trying to build array elements from a list
19909
19953
etc
My thought is in helper column to concatenate the quotes and comma "xxxxx", Then I will copy paste special transpose and should have an array list
I tried ="""&A2&"","
Returns
"&A2&",
But I need it to return "19909",
View 4 Replies
View Related
Jan 31, 2014
In a large range of cells, how I can remove the single quote at the beginning of the contents of each cell? (without single quote they would be formulas). It didn't work using CTRL + L and trying to replace the single quote by blank applying this to the whole range. I am currently using Excel 2003.
Example:
cell a1 contents: '= IF (I17 = "----------", "----------", E17-C17)
cell a2 contents: '= IF (I18 = "----------", "----------", E18-C18)
cell a3 contents: '= IF (I19 = "----------", "----------", E19-C19)
....
etc., etc., etc.
View 2 Replies
View Related
Mar 5, 2009
I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...
View 10 Replies
View Related
Nov 5, 2008
I'm havin' a chart for interest, instalments and so on;
Interest [X%] Loan [Z crowns/dollars]
Loantime [Y years] Instalment [formula, which is "=Z/Y/12"]for each month.
Year, Month, Loan(left), Instalment, Total payment
1, Januari, =Z, =Instalment (constant), =Interest+Instalment
Month, Loan(left), Interest
Februari, =above-Instalment, =X%*Loan(left)/12 (rent is divided per year),
Instalment, Total payment
Constant, =rent+Instalment
and so on... as long as you want ^^ (it's quite messy, but try :D)
The question is as follows "What would a loan of 900'000kr with 9% interest rate totally cost over 30 years?"
[use "IF" to avoid getting anything written after the loan expires]
The question itself can be easily solved by just dragging the formulas down to 30 years and fill in the % and loan etc. But what I do not understand is the "IF" question. I'm supposed to use the IF function to instantly show the cost of the loan (total rent payment) after the 30 years? That's how I understood it. In that case... how do I do that? ^^
View 14 Replies
View Related
Feb 22, 2008
I know how to avoid getting the #N/A in a vlookup, but I have found situations where I have data matches and still get #N/A. For instance, I am looking up an item number (which contains numbers and letters) on one tab to find corresponding info on another tab. I've verified that the item number is indeed in the lookup range but I still get the #N/A. If I copy and paste the item number over the same item number in the lookup range then it works. However, I don't want to do this for each item number.
View 9 Replies
View Related
