I have sample string: Cat jumped over lazy Dog
I only want to capitalize: LAZY
so it should be: Cat jumped over Lazy Dog
Proper capitalizes first letter of every word, just want one word capitalizaed.
Also, if I want to use an and function to capitlize multiple words, so it should be: Cat Jumped over Lazy Dog
where just Jumped and Lazy are capitalized.
I have 30,000 lines and in each line I have words that have all capital letters.
What I want to do, is make only the first letter in each word capital, and the rest lowercase.
What I need to do is fairly simple to explain:
I want to have the first letter of every word in a cell to be capitalized, but the rest of each word must remain as is.
Ie: If I have "My AMD CPU still has some pew-pew" in a cell, I want to be able to use a function, let's say:
=CapFix("My AMD CPU still has some pew-pew")
and the output should be:
My AMD CPU Still Has Some Pew-pew
I've tried using:
=PROPER("My AMD CPU still has some pew-pew")
But the output is:
My Amd Cpu Still Has Some Pew-pew
Which is incorrect (capitalization in the rest of the words "AMD" and "CPU" is lost). Does anybody here know what can be done to solve this problem, or have any info about what I can use to solve it? I have not been able to find any built-in Excel function to solve this, nor ASAP Utilities and I can add VBA code and macros, I just don't know how to manipulate the strings correctly or do the loops, etc that I think will be necessary to solve this.
If anyone could help, it would be much appreciated. I've not seen a single site where a similar problem has been posted. Even if you can't give me a full solution, but can point me in any direction that would be useful, I'd be really grateful.
I have a web query that returns a city and state, but the last character of the state is lower case. I need VBA code to change the last char to upper case. Here is what I have so far:
Code:
tmp = Cells(4, 1).Value
Mid(tmp, Len(tmp), 1) = UCase(Right(tmp, 1))
Cells(4, 1).Value = tmp
Is there any easier way to do this without 3 lines of code?
I want to delete a specific words from string but i have a problem with the code below. For example, i wan to delete the word "Inc" only but the problem with my code is that it is deleting from "Incorporated" too and i want only the code to delete only if it finds the word "Inc" only.
View 11 Replies View RelatedIn column A, I have the following lines:
2014-05-15 02:08:43 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:31:37 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:37:19 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 02:37:20 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 03:07:19 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 15:01:37 @Centre INFO - CHANGE WORLD (Original World to Destination World)
2014-05-15 15:04:46 @Centre INFO - CHANGE WORLD (Original World to Destination World)
I would like to use conditional formatting to highlight cells which have the same first 16 characters (yyyy-mm-dd hh:mm) before the "@" AND that contains the words "CHANGE WORLD". Therefore, I'm looking for a formula I could include in the conditional formatting so I can easily find the "CHANGE WORLD" that occurred at the same time (minus the seconds, they may vary slightly).
I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell. For example A1 will contain the text "Smith Sun Alliance Pension Fund" I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.
View 9 Replies View RelatedIn sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I have the company name in one column. I need a formula to extract first letter of each word and it had to be grouped.
Find the attachment : File1.xlsx‎
Column A has cells showing either Y or N I want Column B to take a Y and convert it to a Yes and N to No. I tried an IF statement but it wont seem to work.
View 2 Replies View RelatedI am trying to find a way of capitalizing the first letter of every word in an excel listing of music. i tried the function that was suggested to me in the help =proper(cell#) and it does not work. i ahve a list of 2000 lines in excel to fdo and would like to do it thequickest possible.
View 13 Replies View RelatedI have this project for work where on one sheet information is put in by typing AF, CF, WF. I need to have Excel read the AF, CF, WF and make that read as the full word. Is there anyway to do that? I can get my boss to have people just put A, C, W
View 8 Replies View RelatedIs there a way to get Excel to automatically change the first letter of single word entry to lowercase in each cell of the first row of my worksheet leaving the case the same for all other letters?
View 5 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 would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
Can I use a string in VBA just like C. I'm trying to get character one by one for a string. Can I do somehting like that here?
In C I use:
char string[20];
For ( int i = 0; string[i] != '"; i++)
cout << string[i];
I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.
View 5 Replies View Relatedhow I check the last 4 letters of a String to make sure they say '.xls', '.csv', etc, etc, for varification purposes.
View 4 Replies View RelatedIf I have the following in different cells of a worksheet:
ANFKLD
or
AAB
and so on
How do retrieve the second to last letter of any of these strings?
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx‎
For icount = 1 To LenComputername
valComputername = Asc(Mid(UserComputername, icount, 1))
Next icount
If the computer name is NAMTOK-PC Then the LenComputername is 9. Does that mean then that the valComputername is equal to 78?
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
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
Does anyone know the VBA line for finding a specific letter in a cell?
eg.
Say cell A1="B"
Say cell C3 contains a word, and I want to know if that word contains the letter shown in in cell A1 (ie. contains the letter B).
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
View 4 Replies View RelatedI need a macro, which removes spaces, symbols, letters, numbers or combination of them from the end of all cells in a column. The symbols should be chosen before the macro starts its work.
For example:
Before running the first macro:
Column A
Word1;;
Word2;
Word3
Word4;
After running the macro the end of the cells should be cleaned from chosen symbols, spaces or signes. (in our example if a cell ends with sign ";" then these signes should be deleted).
Column A
Word1
Word2
Word3
Word4
Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.
Also looking to delete the last "•" char(0149) within any string of selected cells.
What would the syntax for these two functions be?
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 RelatedI have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).
The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.
Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.