Sort Values Which Has Numeric, Spaces And Capital Letter Characters

Oct 8, 2009

I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

I was wondering whether it is possible to find a formula in Excel that would extract (using RIGHT formula) surname from Name Surname cell despite surname lenght i.e.:

'John Smith' in one cell would give 'Smith' in the other 'Tom Let' in one cell would give 'Let' in the other

I have tried to combine RIGHT, FIND and PROPER (for upper case) in one formula but it does not work.

I have many lines of text and I wondered if there is a formula so I can insert a comma before the first capital letter of each line? A small amount of text is below

leave on left Salter Road right Brunel Road

What i would like is there to be a comma before the first capital letter so it reads

I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:

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 have been trying to resolve my latest issue using various IF and Lookup formulas but havent been able to find the answer.

I have designed a spreadsheet that logs staff members hours. Staff input their hours worked in cells C12:BL12 and BM12 uses a simple sum formula to add them all up =sum(C12:BM12).

What i would like to do is input "S" for sick, "H" for holiday, "BH" for bank holiday etc and each one of these letters represent a number of hours adding in to my sum formula in BM12.

For example: Below shows a working week with the total hours worked per day underneath If H=8 and S=7.4, the total would read 39.4

What I am doing is setting up a product ratecard sheet to run with our CRM system in work.

Each product has 10 quantities, with 10 different prices, but each product must have the same product code running down in column A

I have thousands of products to put in to dont want to manually type each code in.

I am ok with Excel, but I can not program in VBA, so i am looking for a formula to use.

The code is ZTRA-00001, which will stay the same for 10 rows, then there will be a space of 1 row and the next code for the next 10 rows in the column would be ZTRA-00002 etc.

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.

A range of cells contain characters that i want to replace with spaces. The problem is that the length of the string differs and also the characters differ.

For example, below a list of cell values:

GR GRKR GRKRKR GRKRK1 GRON GRONBL

As a result for the first value i want 2 spaces, for the second one 4 spaces etc.

Is it possible to do this with a formula? I don't want to use VBA for this if possible...

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)?

Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.

I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.

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

I have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.

However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.

I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.

I have provided an attached example.

Extract numeric value based upon ending characters.xlsm