I am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction?
Is there any way of writing a macro so that specific cells are formatted to start with a capital letter and end with a full stop. Can you also write a macro to automatically spell check specific cells?
I, too, am trying to use excel to the fullest. My first issue is, I would like to convert the numberic grade in one cell to a letter grade in another cell. I am not really good with all the vocabulary, but would love to learn it, and can copy a formula pretty well!!!! Not so good with functions and macros but would love to learn. All help appreciated.
I am trying to do a vlookup. I receive a report that gives the data as hyperlinks. I run a macro to remove the hyperlinks. I'm then left with a name that i want to match using a vlookup to get other data. The names are Chinese characters. I have tried using trim and clean function but its still leaving a space after the name. If i fo in manually and delete the space at the end the vlookup works fine. How to remove the spaces? i have about 5000 rows.
I have a worksheet that contains lots of value. I want to run quick calculation on it but for some reason counta function counts blank cells.
I checked and the cells with =len() and it returns a number > 1
What is the proper way to clean the whole worksheet with some copy paste value and get rid of invisible character like space or nonprintable ones to be sure to get the right number of cells with value?
Initially I'm simply copying a data table from a web page using "Ctrl + A" then "Ctrl + C", and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)
But I keep finding what looks like double-spaces after some of the important text within the Range of cells I'm working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.
Unfortunately, it's not consistence as to how many spaces trail the text I need. Sometimes it's only one space, sometimes its two spaces ?
So far, I've had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don't work all or seem to give any consistent results. E;g; TRIM, CLEAN
I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?
I have a column contains Postal Adress in more than 5000 rows. Column contains Door Number, Area, City and Zip code. I need to separate "Zip code" alone in next coulmn. Zip code (of India) will be in six digits like "600083" also some cases contain space in middle of zip code like "600 083" (after 3 digits). Is there any way to do this without doing cut & paste?
I have a column contains Telephone numbers with or without area code and country code for across the country (India). I need to extract the telephone numbers alone (neither area code nor country code). Telephone numbers will be 6 or 7 or 8 digits (not more than that). Is it possible to extract any set of numbers contain 6 or 7 or 8 digits continously? Some of the cases contains 2 contact numbers (2 set of 6 or 7 or 8 digit characters, between special characters will be there like slash, comma, space, hypen, etc.,
In my column of text strings, I have a multiple format of strings make-up. Below is just one of them I have to check that the first 6 are digits from 0 to 9, and it is followed by a hypen. If condition is true, the first 6 digits is the output (ie. 345678).
However, if I were to continue doing this for other strings, I would soon run out of characters limit that is allowed in a cell. I wonder whether a formulae such as below is valid? Any suggestion or help for a shorter formula is very much appreciated.
The macro I have select 2 columns and 2000 rows. I need a VBA code that will loop through each of these 4000 cells and remove all characters (replace them with blanks) that are not a number, a period or a decimal. Characters from other languages like Chinese, Japanese and Russian should also be removed.
I have something simple (i think) but i cant think it through. I have a range of cells that are populated by a link and so even though the value shows #,### It really shows #,###.########
I would like to trim everything after the decimal point without having to adjust format of the range since some cells are general format and others are Percentage format but they all suffer from .####### HOWEVER, the last digits are variable and never the exact same amount of characters.
for each cell in range("C4:J9") if cell.value "" then Trim(Left(cell,4)) end if next cell
I am trying to write a formula that will recgonise either text or numicial value as the result is used with a match formula. In column C I have data as follows:
1400 SBY 1230 9985 ADO
I am using a =--RIGHT(C4,4) formula in column E to get the required data and then using my match formula to extract other data. How can I rewrite the above formula so that it can read either text or numbers that will allow my match formula to work.
I am trying to sort a database with about 500,000 names. For some reason, about half my data has leading spaces and half do not. I have taken a screenshot at the divide so you can see what I mean. The sort in the picture is in this order:
Column D Column C Column A
Is there any way to mass purge these leading spaces in Column D (column b also has the issue but the fix is not nearly as important as for column D)?
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle E1 8DF - 3 Spaces (8 Char) - This would be correct LL5 1GH - 2 Spaces (8 Char) - This would be correct L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle