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.
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 RelatedI 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 RelatedI 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?
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
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 RelatedIs 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 RelatedConvert 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
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 View RelatedI just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
I don't know what formula to use to do the following:
In Column E there are cells which contain numbers which fall between 0 and 400. What I am trying to do in column F is show the a letter (E, D, C, B, A, A*) if the number in column E fall between a range.
For example:
If column/row E2 is between 160 and 199 it equals E, if column/row E2 is between 200 and 239 it equals D, if coulmn/row E2 is between 240 and 279 it equals C etc etc....
I am trying to allow users to select a file, and then the file name/ the file path / and date are added.
Right now, all those functions work in the following code, but the file path shows up as a letter drive. As there could be upwards of 50 people using this file and the mapped drive could be different, i wanted the full path to be displayed.
Code:
Private Sub File_1_overwrite_Click()
Dim filename As Variant
Dim filename1 As Variant
filename = Application.GetOpenFilename(, , "Select Programme")
filename1 = filename
filename1 = Mid(filename, InStrRev(filename, "") + 1)
File_1_link = filename
file_1_name = filename1
File_1_date = Format(Date, "MM/DD/YY")
I have 2 cells, one has a first name the other has a last name for instance:
A1 = Joe
B1 = Smith
I am trying to combine the first letter of the name in A1 with the next 3 letters in B1 so that C1 = jsmi
I am trying to make a puzzle and use excel to verify I have done it correctly.
What I want to do is when I type a letter in a specific cell have it put a number value in another cell automatically. For instance, if I type the letter "a" in cell A1 I would like the number 60 to populate automatically in cell B1. I want to give each letter in the alphabet a numeric value so I can type a message in the A column and then auto sum the results of the numbers in the B column.
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
I have a validation drop down box type thing... when I select the drop down, the lower-most option is highlighted as a default. How do I make it so that the upper-most option is highlighted first?
View 9 Replies View RelatedI am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
I have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS
View 2 Replies View Relatedhow it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,
View 9 Replies View RelatedI want to put the letter A in cell a1(or any cell in column A, SHEET1) of SHEET 1 and get the information from SHEET 2, column A. Put this information in column B sheet 1. I will have about 40 columns in sheet 2. I used a if formula, but only 8 ifs can be used.
View 9 Replies View RelatedI need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
I'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
View 3 Replies View RelatedI have project's start date and end date and total cost. I want to spread the total cost across month on top using the total number of days[calculated using the end and start date] that project will run and the number of days that project is in that month. I have highlighted the area in yellow where I want formulas to work.
View 3 Replies View RelatedWhats the correct code to use on a userform text box which when you type in it, it is all in capital even if you have not got caps lock on.
View 3 Replies View RelatedI have the following letters in cells
D2= A
D3=G
D4 =L
These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,
Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.
I am attempting to create a capital depreciation schedule that gives us our total operating impact for a given calendar year. I can populate my depreciation based on the purchase month, and fill across the schedule the monthly depreciation cost, however, I'm not sure how to stop the depreciation after the item has depreciated fully (say after 36 months).
For example, if someone were to select March 2013 in B5, monthly depreciation should begin in E5 and spread across ONLY 36 months. If they were to select April 2013, the depreciation would begin in F5 and extend ONLY 36 months.
I think it might be possible to do this with an COUNTIF function, but it is not working for me.
How to create space between two consecutive capital letters?
like i have....
RPerry
RCCox
i want...
R Perry
R C Cox
How to create space between two consecutive capital letters?
like i have....
RPerry
RCCox
i want...
R Perry
R C Cox
I just want to conditionally format some columns with different colours. The problem is that I have some cells that have the value 'a' and others that have 'A'. These should be different colours, but I can't seem to get Excel to detect between the two forms.
View 9 Replies View Related