What i need is the following: In cell B1: if A1 is greater than 08:00am but less than 14:00pm than B1 should have a tick which is green, if cell A1 is greater than 14:00pm than B1 should have a cross which is red...
View 7 RepliesI have created a multiple choice quiz maker that randomizes the questions and responses. This means that the quiz questions/responses must be copied and pasted each time a new quiz is generated. But in doing so, Excel loses formatting (such as super and sub scripting) and some special characters (like pi, alpha, the degree symbol).
Is there a way to get such things to copy properly from cell to cell, sheet to sheet, using Excel?
I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.
I know the LAN function, but I don't know how to make it work for the conditional formatting.
How do I add special characters in-between text? I know how to include a registered sign by using =CHAR(174) but what I do not know is how to include text before and after the sign itself.
ie timeware *registered sign* community
Is there a way to check a special character in textbox?
For example if type "TWO*" then i press a button it must raise an error.
my requirement is to create db2 query with the existing excel sheet data.
Cells in the excel sheet contains special characters like { ) , ( , ’, ; , , , _ , ... }
These cells has to be merged, is there any function in excel to do that?
Concatenate function is not working..! getting data error
I need to fill out a web form and have been doing so like this:
With hDoc.forms(0)
.NameOfInputBoxWithinTheForm.Value = "AAC"
.NameOfInputBoxWithinTheForm.Value = "1/1/7"
.Search.Click
End With
I have recently run into forms where the name/id of the input element contains special characters. Like Name#OfInputBoxWithin$TheForm$1. which VBA wont allow me to use in the code above since they are type def characters.
In order to compare strings I have to remove a special character from imported text.
It is character F008 from Unicode(hex).
This charcter is not on my keyboard.
I find it under Insert - Symbol but I can't copy and paste it to the find/replace window.
how can I key in this character so that I can use it for find and replace?
I was hoping someone could help me out. I've been trying to put together a formula to find special characters with no luck.
Characters I’m looking for are:
I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER). How do I search for this special character to replace it? How do I identify what that invisible character is?
View 2 Replies View RelatedI am trying to extract text from stings that appear in the following format XXX-XXX-XXX and XXX-XXX-XXX-XXX.
I have figured out how to extract the first and second sets of data, what I am stuck on is getting the third and (if present) fourth sections.
I want to remove all the special characters i need only texts and numbers.
View 6 Replies View RelatedI am looking a macro which should perform below activities
I have a lists of suppliers which needs to cleansed....
1.replace all the special characters with a space
2.replace Corporation with Corp
Incorporation with Inc
Limited with Ltd
Limited partnership/ltd partnership with LP
Company with Co
ASSC/ASSOC with ASSOCIATION/ASSOCIATES (SEARCH SUPPLIER IN GOOGLE FOR THE CORRECT ONE)
MGNT with MANAGEMENT
SVCS with SERVICES
DEPT with DEPARTMENT
Unltd with UNLIMITED
Ctr with CENTER
UNIV with UNIVERSITY
3. Remove any text, Special characters and numbers after INC,CO, LTD, CORP,LLP, LLC
The macro should when I select any particular column... not restricted to column A
I need to check whether a description of a certain product in my excel has any of
below special characters
! @ ' " ] [ } { | & $ # ^ ~ % ®
and also the description should not exceed more than 40 characters.
i tried using the "If" condition but it does not seem to check the same.
I am using Excel 2002 SP3. I have a multiple worksheet workbook and am using links between some cells so as to keep the updating down to a minimum. Typically I can enter [code]=' Storage '!E70[code] to transfer the contents of one cell in the ' Storage ' worksheet to the cell I want the data in and I get the value of that worksheet/cell. But sometimes all I get is the data I entered: ie ='Storage '!E70. Is there some for cell format problem that prevents this from happening?
View 2 Replies View RelatedI have data in column A. The data contains a name, an underscore and initial/s. For example: rebecca_rt, john_j, etc... I'm trying to pull only the characters to the right of the underscore. I played around with the below formula that I found under a similar question thread. But, it doesn't quite work. As it is now, it returns ecca_rt when I just want rt. I'm sure this is probably really close =IF(ISERROR( FIND("_",A1)),A1,RIGHT(A1,FIND("_",A1)-1))
View 3 Replies View RelatedWorking on a macro to replace a list of about 20 specific "Special" Characters in excel, and have ran into 6 that will not work.. Following are the characters: ā, č, ć, ř, ş, ż
ActiveSheet.Select
Selection.Replace What:="ä", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="á", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
I have inherited a spreadsheet that will prompt the user for a text file to import and it will split each line into different cells based on column widths. The problem is that one field can contain non-printable characters that are causing the macro to split the data into 2 lines.
I think one of the special characters is a new paragraph but there could be others.
I need help with replacing these special characters with a space prior to spliting the line into an array.
I have conditional formatting set to hilight duplicates, but I a, wonder if there is a way to exclude special characters (in this case specifically dashes).
View 5 Replies View Relatedi have data which has lots of these hyphen "-" how can i get rid of them,
for example -Eq Cash-
i need the result to be
Eq Cash
Also i have lots of front spaces in my data, how can i get rid of those? so for example
Test (there is 2 spaces before T)
I am aware that special characters such as -,(,),[,] are not permitted in named ranges (for lists), but is there a workaround for this? I have a lot of dependent lists that need to access names that contain either a - or () or both. I could simply remove the -'s or ()'s using find/replace, but they are required, as they're part of the name. I am aware of this method http://www.contextures.com/xlDataVal02.html#Illegal but I have a lot of dependent lists across a sheet, and a VLOOKUP isn't going to work. If there isn't a workaround, are there any allowed characters I could use to replace the -'s and ()'s?
View 3 Replies View RelatedThis formula I want to apply it in another workbook. It split in different columns the content of a cell.
The formula is below:
[Code] .....
In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|
If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.
I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.
I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.
I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.
If I can determine the position of the last occurance of the special character, I could use the LEFT function.
The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.
Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.
There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.
I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.
I would like to know if it is possible to format a cell where you enter a date (08-02-2009) and it returns "08-02-09 thru 08-08-09" that weeks date range in that cell.
Format custom mm-dd-yy " Thru " mm-dd+6-yy
I know will not work but is there a way to do this??
I dont know the correct terminology with whick to phrase my question, but I would like to know if its possible to copy say columns A-J on "worksheet A", and copy columns A-J on "worksheet B", each of which have different column widths, and paste them both to "worksheet C"?
My problem is columns A-J on "worksheet A" are perfect and copy and past fine to "worksheet C". But when I copy columns A-J from "worksheet B",which have different widths, and paste them to "worksheet C" right below what I had previously pasted from "worksheet A", the column widths interfere with each other.
I have tried a special paste, and it seems to re format everything above the current page its pasting?
I would need support for the following:
I have one column with a date 26.09.2014 and one with a time: 09:15
I would need to merge both cells to be displayed as per this format: 26/09/2014 09:15
I use this formula: =CONCATENATE(A1;" ";B2) and get this result: 41908 09:15
I've tried to change the date format in first cell but nothing works and I always get the ref. number of the date instead.
I would like to use the following line to calculate the formula,
In the format i have already specified.
But to leave the values in the cells apposed to the formula.
This way when i delete the source sheets the results will still be left as values not formulas.
It can probably be done in 3 lines but i have to do this for 9 different rows so was wondering if there is a way of combining the three tasks into one row
i have 2 listboxes with numbers if user has selected choice in both then i want to enable okbutton. i tried in okbutton's mousemove: if listbox1.selected = true AND listbox2.selected = true then okbutton.enabled=true.
View 3 Replies View RelatedI have 9 worksheets in a book with sheets 1-8 used for data entry, and sheet 9 used for a weekly data upload. Sheets 1-8 are all formatted the same, they just represent different vendors. I need a macro to look through column F (invoice number) on sheets 1-8 and see if the value of each cell already appears in column c on sheet 9. If the value is not found and the row value for column M (sheets 1-8) is >0, I need to copy the data from columns D:K and paste special values into the next blank row on sheet 9.
Basically I'm trying to see if I've already paid an invoice, and if not then I need to automatically add the data for payment. I've tried to mess around with a VBA code myself and ended up with a migraine every time. I've also tried to use the advanced filter, but the cell values I need to copy over contain formulas and conditional formatting...not to mention I can't delete the data on sheet 9 that has already been added.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx