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?
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 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 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 have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
I am looking for a code to insert alt-enter into a cell or a column with a defined comma count per line, saying 5
is
C1,C2,C3,C4,C5,C6,C7
C8,C9,C10,C11
want to change the cell(s) as
C1,C2,C3,C4,C5,
C6,C7,C8,C9,C10,
C11
Need a function that would insert a letter or a number in front of numbers in a cell for example
column A
3245
I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with
Column A
S3245
I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.
ABCD90bj10r7
ABCD,90,bj,10,r,7
ABCD90rt8r7
ABCD,90,rt,8,r,7
iFH15jr7ri12
iFH,15,jr,7,ri,12
iFG155jr8ri11
iFG,155,jr,8,ri,11
iFG15jr8ri9
iFG,15,jr,8,ri,9
i'm a complete excel novice, but i think this should be pretty easy for you guys...
i just bought a new satnav system after 4 years with tomtom...
anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:
-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,
thing is, most of them don't have the 2 commas at the end and just end with the number, like this:
-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247
therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...
i'm guessing for anyone with excel experience, this should be pretty easy to solve...
Original table:
Column1
Column2
Column3
Column4
AAA
DDD
A1
X123, Y123, Z123
[Code] .......
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 column of data that is given to me that is a mix of letters and numbers and I need the numbers to have leading zeros, they must all be three digits. The data has either 3, 4, or 5 letters followed by numbers 1 through 999. Example: ABCD7 I need to change it to ABCD007. I am using Excel 2004 for the Mac.
View 9 Replies View RelatedI'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 RelatedComma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
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 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
How to know the position of third comma in the same cell.
I've a column which contains data in below format
alves, martine
burraq, joys
ande, kallis
I want to know the LEN of words before the comma, which would be as in this case
5
6
4
I need a formula for this.
I have a UserForm with different text boxes, everytime I put a number with decimals (ex 100.23) on a Textbox and I want that number to be shown on a excel cell, VBA changes the "dot" for a "comma" so excel understands it as an integer number 100,23 (10023).
View 9 Replies View RelatedI am an Excel Novice. I don't know very much at all about how it works, what formulas are or any of its intracacies. My job requires me to take information from an Excel file that is emailed to me.
I need to know in the simplest terms how to take the first column (which is last names) and add a comma to the end of each name (one in each cell) all the way down that column, before I copy and paste them, so they will read "last name comma space first name".
I 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 need to remove the last character in a cell if it is a comma. I can't remove all commas because there are other commas in the text.
View 2 Replies View RelatedIs there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
View 14 Replies View RelatedI have a cell in a table containing user entered Comma separated values.
How can I process it so that user entered values can be referred to just like in an array? Is it possible to do something like cell.value[0]?
For example --
cell A1 has values red, blue, green. B1 has a formula that can refer to "red" in A1 by writing something like A1.value[0]
Is it possible to have a function return values to another cell reference than from the cell where it is called? Like a UDF called functionIndividualValues(InputcellReference, OutputCellReference) functionIndividualValues(A1, A2) meaning that the input string for the function is in A1 and the output should go to the cell A2.