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).
ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.
A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'
I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:
City
01
Express
02
Overnight
03
So "C" would return 01, "E" would return 02 and "O" would return 03.
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 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 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
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.
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View RelatedWhen 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 RelatedHow to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
I 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 am trying find a match from multple "text" values.
The values I'm using are flight numbers from sheet "Indiv case" in column (range H2:H51). The flight number could occur multiple time in the column.
The associated flight number sheet "Code & categories" in column (range H2:H257) are associated with the last port of embarkation (range I2:I257) in "Code & categories" sheet.
I need to copy & past the name of the Last port of embarkation from sheet "Code & categories" into sheet "Indiv case" adjcent to the flight numbers in column (I2:I51).
Example: Sheet "Indiv case" from Column (H2:H51) Fligh number Data: UA863, VA4148, EK432, BA15, BA15, VA98, QF8, AC33 etc Using these value from "Indiv case" from Column (H2:H51) search and match valuse in "Code & categories" in column (range H2:H257)
If match found copy valuse from sheet "Code & categories in column (i2:I257) in to sheet "Indiv case" into column (I2:I51) Last port of embarkation".
H2;H257, I2:I257
Flight, Last Post
3k111, Singapore
3k131, Singapore
AC33, Vancouver
Copy and Past "Last Port" into sheet "Indiv case (I2:I51) adjcent to matching flight code.
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
Here is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.
Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84
As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.
I have to loop through a range in A, and if the letter "C" or the number "9" appears in the cell anywhere (it won't be a whole cell value) then I need column B to show "C".
I know how to do a whole value loop, but I'm stumped on a 'find X anywhere' search.
I need to check if a cell ends with {"a","b","c","d","e","S","t","v"}. and if it doesn't diplay a warning
this is what i have
--------C
10----4444s
=IF(RIGHT(C10,1)={"a","b","c","d","e","s","t","v"},"GOOD", "Must enter Suffix Letter")
Is there a way to lookup the first letter of a word in the cell. I am trying to keep my sheet as small as possible for emailing. It would help to narrow down the possible lookup combinations. For example I only need to know if it starts with T, P, or V. I don't need to know the rest of the word. eg TMO, TAEFA, P1284, VTL3D etc.
View 2 Replies View RelatedI have a colum with 350 cells in use, each of these cells contain a 3 digit number. Without having to go into each cell and type is there a way i can put the Letter
"R" infront of each of the 3 digit numbers?
I have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?
View 9 Replies View RelatedI need an excel cell formula that will detect whether or not the word in a cell contains the letter 'R' or not.
ie. If cell A1 contains a word that contains the letter 'R' then return a '1' otherwise return a '0'.
I 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 one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
I have specific need for text manipulating formula. Is it possible to reverse text in a cell, for example: microsoft excel to lecxe tfosorcim.
View 12 Replies View RelatedI have a workbook and I want to check in a cell, if the value in the cell starts with "S" or "D". In VB.net there is a method .StartsWith to check the starting letter in a file. Is there a way to check the starting letter in the cell?
View 2 Replies View RelatedI WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS
IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE
ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF
NUMBERS.
I'am trying to add a row of cell that contain a letter befor the number
I need to add-up the numbers and ignore the letter
I a protected workbook I have the rows and columns indicated below. Each letter in the 2nd row is in a single cell. When completingthe entire form I want to be able to have the appropriate letter circled. I am not permitted to change this selection process to a drop down list - it must remain as formatted.
View 2 Replies View RelatedI have a list with a column where all cells begins with the letter D. I would like to remove that D from all cells. For instance;
Dxxx1
Dxxx2
Dxxx3
Dxxx4
Should be;
xxx1
xxx2
xxx3
xxx4