Weird Character In Cell Using VbCrLf
Sep 6, 2009
In the following code snippet;
strFill = "Query for" & strCommName
strFill = strFill & vbCrLf & strPitHrs
strFill = strFill & vbCrLf & "This query has a rating of"
The cell output is formatted this way;
"Query for SP500
9:30-4:15 EST
This query has a rating of"
And thats how the text shows if you copy and paste it BUT in the cell there is a square box at the end of each of the first two lines. Is the vbCrLf causing that? If so what should I be using instead so the text in the cell shows exactly like the bolded text above
View 9 Replies
ADVERTISEMENT
Feb 19, 2008
I'm trying to sum a certain column. The column is dynamic and depends upon the threshold. So this is what I have
Sheet1
Div | 100+ |130+ |140+ ( might tell me (140+)
View 9 Replies
View Related
Mar 17, 2009
I have some weird problems that I don't know how to fix (or even explain). Mostly, my code is good and works well in all my spreadsheets but I find myself trying to debug stuff that should be working, but isn't working 100% as it should.
For example, I have a cell that counts information using
=NB($A$8:$A$1000)
BTW: NB is COUNT in french, just so you know...
The weird problem is, it seems to skip the first 10-15-20 rows; it doesn't count them. So the total in the cell should be around 150, but it's 129?...
Can someone give me an alternative code to count cells?
View 9 Replies
View Related
May 30, 2008
I have a client with a list of books, and they are trying to sort a column in a sheet that has LC classification codes. When just doing a normal A-Z sort, it looks like this:
RJ 41 .D53 F54 2005
RJ 416 .L4 C52 1999
RJ 420 .D5T97 2003
RJ 421 .H385 2006
RJ 426 .H43 P43 2005
RJ 431 .M37 2003
RJ 436 .A8 R33 2006
RJ 436 .A8 T498 2001
RJ 436 .A8 T87 2003
RJ 45 .N4 2000
RJ 45 .N418 2006
RJ 45 .R92 1998
RJ 47 .E39 2001
RJ 48 .H37 2000
RJ 48 .H37 2002
RJ 48.2 .C537 2004
RJ 48.2 .P347 2005
RJ 48.2 .P385 2005
RJ 48.2 .R93 2003
RJ 480 .P34 2005
RJ 482 .R48 L448 2004
RJ 486 .C45 2000
What is weird about it is that I would think that the numbers after the RJ would be in numeric order but it seems to be treating them as codes. So, instead of RJ 41, then RJ 45, it goes on with a series of things that start with RJ 41. Is it normal for this to happen, or is there a way to change it so that it goes 41, 45, 47, 48, then 416, 420, etc.
View 9 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
Oct 21, 2008
Data > Import External Data > Import Data
and specified that its a delimited text and the delimiter is a comma ",". One of the columns in the row has got a pretty long text and contains certain new line characters as well. After extracting the CSV the data in this particular column is treated as a different data entries.
Somebody please help. I have added the file in xls format since csv upload is not permitted.
View 6 Replies
View Related
Sep 2, 2009
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.
View 2 Replies
View Related
Dec 12, 2005
A client of ours has one particular file that has some very strange behavior as described below. This only occurs with the one particular file and the behavior is consistent across different PC's in both their office and ours. All other Excel files in both offices work fine. Here's what happens:
- you can use Windows Explorer to copy the file between drives without
a problem (as expected)
- if you open the file in Excel on one drive then save it back to that
same drive with either the same file name or a different file name, the
file works fine (as expected)
- if you open the file in Excel on one drive then do a "Save As" to
save it to a *different* drive with either the same file name or a
different file name (in default .xls format), the new file causes
errors in Excel every time you open it as per below (weird!)
- even if you do a "Save As" and choose a different format eg Excel
97-2003 or Excel 95, the error behavior is consistent (weird!).............
View 2 Replies
View Related
Nov 27, 2012
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
View 1 Replies
View Related
May 18, 2008
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
View 5 Replies
View Related
Mar 1, 2012
I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.
I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).
My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub
This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?
View 3 Replies
View Related
Feb 17, 2014
Can I get a formula for the below specification.
if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010
View 1 Replies
View Related
Dec 10, 2009
i have a column that is 1900 long and i need to add a "comma" to the end of everything in each cell in that column. How do i go about this? so at the moment it look like this:
random1
random2
random3
random4
But i need to look like this:
random1,
random2,
random3,
random4,
View 3 Replies
View Related
Apr 28, 2008
I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.
View 14 Replies
View Related
Oct 12, 2009
I have a number in each cell 42189654 and I want to add a period in the 3 place. Example 42.189654. I cannot seem to get the correct formula.
View 5 Replies
View Related
Jan 31, 2013
I'm using the hidden-row trick to auto-fill cells beneath the hidden rows.
For this to work, every cell that is to be auto-filled in must have a character in every cell above it.
How can I make sure that my unfilled cells always have a character in them? If a user deletes the contents of the cell, I want a single character to stay in the cell (I want a backslash). This way, there will always be either a backslash, or the user-filled data in the cell. It can never be empty.
View 3 Replies
View Related
Mar 15, 2014
I have following kind of sting in the cell.
<Abc_desc><to_location>10102535</to_location><from_location>100</from_location><abc_nbr>00000000009882203100</abc_nbr><abc_type>C</abc_type><con_qty>113</con_qty><bcd_nbr>00000000009882203</bcd_nbr>
I want to extract the number between <abc_nbr> and </abc_nbr>. i.e., "00000000009882203100".
The starting number of the string may varies.
Macro should pick the number and paste it into the next cell.
View 14 Replies
View Related
Mar 7, 2012
I have a column and each cell in that column has information that ends with a comma "," I would like to remove that comma.
View 4 Replies
View Related
Oct 25, 2013
I would like to take a value in a cell and remove the second and third characters in that cell.
For example: "V0010" would become "V10."
I've found formulas for removing the beginning and ending characters, but not a way to remove from the middle.
View 2 Replies
View Related
May 27, 2006
I am using an exported dbf from filemaker pro. This is an established dbf, with over 2,000 sku's in it. Here is what I want to do.
I am using Indata and InDesign to create a catalog. I need for excel to pull the first character from a cell and put it in another cell. i.e.
I have a dbf where I have author last name, and title of book. I need for excel to pull the first character of each of these fields and put them in their own cell/column. This will allow me to use these new columns to make the " headers" for my InData/InDesign doc.
View 4 Replies
View Related
May 1, 2007
I am creating a macro that saves a file using the value in X1 as a variable name to save the file. I want to check if the last character in cell X1 is a period ("."), and if it is I want to either remove the period from X1 and continue to save, or tell the user it is not possible to save the name that ends in a period.
View 2 Replies
View Related
Oct 18, 2007
I enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.
View 6 Replies
View Related
Sep 12, 2013
I have instances such as:
A1: 2' x 3'
A2: 2'6" x 8'
A3: 3' x 1'2"
and would like to convert them to:
B1: 2'-0" x 3'-0"
B2: 2'-6" x 8'-0"
B2: 3'-0" x 1'-2"
I am lost trying to figure out how this is accomplished.
View 3 Replies
View Related
Feb 25, 2014
Essentially what I'm trying to accomplish is import a file from Quickbooks and determine whether it was a Credit Card, Check or Invoice based on the account number.
For example:
45-12345 should return the value CC in an adjacent cell because it contains the "-" character.
I0123456 should return the value INV in an adjacent cell because it contains the "I" character.
01234567 should return the value ACH in an adjacent cell because it doesn't contain either character.
I tried using VLookup, but I can't figure out how to write a formula for when it just contains a character, only if it matches it or is text/number.
View 4 Replies
View Related
May 14, 2014
I have a requirement... which need to search from a cel if it march then replace with below name. When user gives a selection screen, month displays in a cell A1.
For example: If this cel (A1) is jan then it should replace with Jan, if mar then replace with mar, if MAI then replace with MAY, if sep then replace with SEP, if OKT then replace with OCT.... and so on...
or
It search the cell A1 for MAI, then replace with MAY & also should search for if A1 is OKT then replace OCT, & also if A1 is DZC then replace with DEC.
I know the function of ....
[=IF(ISNUMBER(SEARCH("*MAI*",A1)),"MAY",A1)] =
This formula brings me only 1 search, but I need 3 search if match replace with respective character. this should search for OKT & DZC ALSO....
present - new
JAN - JAN
FEB - FEB
MAR - MAR
APR - APR
MAI - MAY
JUN - JUN
JUL - JUL
AUG - AUG
SEP - SEP
OKT - OCT
NOV - NOV
DZC - DEC
View 4 Replies
View Related
May 19, 2009
I'm trying to speed up some work on an excel sheet. The sheet converts degrees latitude and longitude to Northing and Easting coordinates, but right now we have to type them in directly.
For example, the degrees would be 81.0056 and I want to reference just the .0056 from the cell that contains the whole 81.0056 degrees. Is there any way to just reference character place three through seven of that cell? All I'll need it to do is copy it over, the rest of the program will convert it for me.
View 9 Replies
View Related
Sep 2, 2009
I need to remove the last character in a cell if it is a comma. I can't remove all commas because there are other commas in the text.
View 2 Replies
View Related
Mar 24, 2014
A1 value: HARD COVER + SCREEN PROTECTOR - IPHONE 5
B1 value: WH
Want to search for the character "-" and replace it with "WH" from B1 Cell
Do this for all $A column
View 2 Replies
View Related
Jun 3, 2014
I am trying to create a button that when clicked it will add a character to the existing cell. Eg if cell had LDs and I click button I like it show LDs₂
The ₂ is character code 2082.
View 5 Replies
View Related
Mar 6, 2014
The script below is used to delete the first character of every cell. I want the commas ',' in the cells starting with a comma to be deleted.
However, it ends up that even for the cells not starting with a comma, the first character is deleted.
[Code] .......
View 2 Replies
View Related
