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.

View 9 Replies


Convert Lower Letter To Capital Letter Automatically

Mar 2, 2009

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?

View 9 Replies View Related

Mid Find First Capital Letter

Nov 1, 2009

I require a formula which finds the he first capital letter within a cell and returns the text, but I need it to ignore blank cells and the symbol *.

View 6 Replies View Related

How To Use RIGHT Formula To Show Value Until Capital Letter

Dec 29, 2011

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.

View 2 Replies View Related

Insert Comma Before First Capital Letter In Cell

May 10, 2014

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

leave on left, Salter Road
right, Brunel Road

Is this even possible?

View 14 Replies View Related

Code Not Working With Input Capital Letter

Jan 24, 2014

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:

If Range("H13") "s" Then

I tired this:
If Range("H13") "s" or "S" Then

View 3 Replies View Related

Using Countif To Count Only Capital Letter (D) And Lower Case (d)

Jun 27, 2014

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?

View 12 Replies View Related

Specific Cells Are Formatted To Start With A Capital Letter

Jan 3, 2008

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?

View 11 Replies View Related

Convert First Letter Of String To Capital Without Altering Formatting

Nov 17, 2009

Convert the first letter of a string to a capital without altering the rest of the strings format.

example: the activecell contains the string a1st (lowercase a, superscript 1st)

how can i achieve this in VBA?

Additionally, is it also possible to convert all of a string to capitals except the superscript characters

View 9 Replies View Related

Changing Sort Order - First Text Then Numeric Values

Nov 3, 2011

Currently I have a macro set up that sorts a range.

It sorts numbers first in ascending order and then text comes at the bottom of the sorted range.

Is there any way of reversing this so that text appears first and then numeric values in ascending order below?

View 1 Replies View Related

Assigning Numeric Value To A Letter

Mar 18, 2013

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


Is this possible?

View 1 Replies View Related

Sequential Alpha Numeric Code In Batches With Spaces

Mar 12, 2013

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.

View 1 Replies View Related

Converting A Numeric Cell To A Letter Grade

Feb 14, 2009

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.

View 11 Replies View Related

Replace Characters With Spaces?

Dec 10, 2011

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:


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...

View 5 Replies View Related

Remove Spaces From Chinese Characters

Jan 9, 2012

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.

学员名字EliteNumber刘传佳=TRIM(A2)=VLOOKUP(B2,Sheet3!B:D,3,FALSE)刘传佳=VLOOKUP(D2,Sheet3!B1:D3550,3,FALSE)刘传佳 =TRIM(A3)刘传佳=VLOOKUP(D3,Sheet3!B2:D3551,3,FALSE)钟才富 =TRIM(A4)钟才富=VLOOKUP(D4,Sheet3!B3:D3552,3,FALSE)张杰 =TRIM(A5)张杰=VLOOKUP(D5,Sheet3!B4:D3553,3,FALSE)
刘传佳刘传佳刘传佳2001425刘传佳 刘传佳 刘传佳2001425钟才富 钟才富 钟才富800857张杰 张杰 张杰#N/A

View 8 Replies View Related

Deleting Certain Characters Until The 1st Letter Of Every Row

Apr 8, 2014

Let's say I have these 4 rows

1231-123 Lady Gaga - Applause
3123 123 -- Marvin Gaye - SEduction
1-20389@---12 Beyonce - Crazy in Love
-123 -- Andrea Bocelli - whatever

Is there any way to make Excel delete every character that is not found withing the alphabet until it find the first letter and STOP there ?

View 11 Replies View Related

VBA - Removing Front Spaces And Special Characters

May 26, 2009

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

View 9 Replies View Related

Getting Rid Of Every Non Numeric Characters

Jan 13, 2007

I have a cell that contains BYO CUSTOMER XYZ 76458992

I want the cell next to it to remove all of the letters and display only 76458992.

View 9 Replies View Related

Cleaning Workbook With Spaces Or Invisible Characters In Cells?

Sep 26, 2012

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?

View 9 Replies View Related

Remove Characters / Spaces After Text For All Cells Within Range

Oct 12, 2013

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 ?

The start of my code reads:


Sheets("Data").Name = "Old Data"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Data"


' At the moment I'm using to select the pasted range I want to work on: Range(Range("C46"), Range("C46").SpecialCells(xlLastCell)).Select

This is where I need a code to work on the new Data sheet and remove all the trailing characters.

MsgBox "All data cleaned successfully !", vbInformation + vbOKOnly, "All Done"

View 3 Replies View Related

Extract Numeric Characters

Jul 1, 2009

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?

View 9 Replies View Related

Extract Set Of Numeric Characters

Jul 3, 2009

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.,

Here are some examples: ....

View 9 Replies View Related

Check If 1st X Characters Are Numeric

Aug 10, 2007

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


=IF(ISNUMBER(--(MID(A1, FIND("-",A1)-1,1))--(MID(A1,FIND("-",A1)-2,1))--(MID(A1,FIND("-",A1)-3,1))--(MID(A1,FIND("-",A1)-4,1))--(MID(A1,FIND("-",A1)-5,1))--(MID(A1,FIND("-",A1)-6,1))),LEFT(A1,FIND("-",A1)-1))

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.


View 4 Replies View Related

Remove All Non Numeric Characters

Oct 16, 2007

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.

View 3 Replies View Related

Trim Numeric Characters After The Decimal

Feb 24, 2009

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
end if
next cell

View 9 Replies View Related

Formula: Last X Characters, Text Or Numeric

Dec 7, 2006

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

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.

View 5 Replies View Related

Leading Spaces Causing Sort

Jan 30, 2010

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

View 2 Replies View Related

Extract Last Letter And Remaining Characters From Alphanumeric String

May 14, 2013

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.

Sub-base A 2-3
Sub-base B 3
Paving C 3-4

View 7 Replies View Related

Excel 2007 :: Ensure Cell Postcode Have 8 Characters (including Spaces)

Oct 8, 2012

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:

EH21 6PQ - 1 Space (8 Char)
EH12 9HG - 3 Spaces (10 Char)
E1 8DF - 3 Spaces (8 Char)
LL5 1GH - 2 Spaces (8 Char)
L5 1FG - 1 Space (6 Char)

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

View 9 Replies View Related

Extract Numeric Value Based Upon Ending Characters

Jan 10, 2014

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

View 5 Replies View Related

Copyrights 2005-15, All rights reserved