i need to find a way to search for numbers in a cell that are attached at the end of a group of letters. ex. (xxxxxxxxx01-01-001). i want to search backwards in the cell going right to left. what i need to do is once i find the numbers i need to go to the last number ex. (......x01-.....) and in front of it place a space ex. (......x 01-.......). right now i havent come up with a formula that can do this.
View 9 RepliesHow would I verify a postcode format that starts with a number followed by one or two letters, space, number, letter, letter, if correct displays correct if incorrect displays incorrect
View 14 Replies View RelatedSo I have 2 sheets from a much larger worksheet where I wish for the first sheet to extract a row of values, one at a time from the second sheet using a range of numbers I enter as reference for where to look for the data. Sheet 1 can be thought of as a summary page and Sheet 2 is where data is stored.
I need to first check if any data under Sheet 2 column B fall within a specified range of numbers and if any of those numbers are found I want it to grab the largest and latest number from that range and pull all the data from certain columns in that same row and place it into Sheet 1, one column value at a time.
Sheet 1 A1 = A range of numbers as text. 10-15, 16-20 etc Only one range is entered. This also tells me what results I'm looking at.
Sheet 1 B4:B14 = Destination cells for the individual data I'm wanting to pull from Sheet 2 columns H:P. B4 wants H, B5 wants I, B6 wants J etc
Sheet 2 column A = A3:A102 are numbered 1:100.
Sheet 2 column B = A series of numbers anywhere from 10-70. Always in sequence and they can repeat. 10,10,11,12,13,14,15,15,15,16 etc. Ranges from B3:B102. Only one number per cell.
Sheet 2 columns H:P = Data in H3:P102 I want to extract to Sheet 1 B4:B14.
Now lets say I want a formula for Sheet 1 B4 which wants a value in Sheet 2 H. If Sheet 1 A1 = 10-15, I want to check whether Sheet 2 column B has any values equaling those and then tell me which row that last number appeared in.
Example: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5. (B6 is outside range and B4 isn't the last time the 14 appears.)
Then, knowing B5 is the value I want, find which row it is in (row 5 in this example) or use the number in A5 (3) and then find my way to column H (H5) where the value I want to pull is.
Example 2: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5.
Sheet 2 H4=10, Sheet 2 H5=32, Sheet 2 H6=42. Place "32" from H5 into Sheet 1 B4.
I'd like to also have some error control so I'm not trying to pull data from blank cells if it's relevant. Maybe check if Sheet 2 X3=0 and if it is, do nothing as no data appears if the cell is 0.
I have put a lot of time into trying to solve this myself but I feel way out of my depth. I've tried going step by step but I can't seem to figure out which functions are relevant and also things like how to return the range that the A1 values appear in or if using MAX, not having it return values outside of A1's range also.
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies View RelatedI have the following column:
A1 B1 B3
Ab123 1278 what i would like is if cell A2 start with AB then B1 and if not then nothing
AC125 1587
AF123 1365
AR125 1259
I would like another cell to check the cell where i have the two letters and the numbers. if the cell start with the number that i am intrested then to confirm it to me, or to give me a value from another cell.
I am trying to count the number of certain letters in a given cell. I figured out the formula when there is not a repeat of a targeted letter. For example, if multiple C's appear in cell A1 I will only get a value of 1 and not the exact number.
Here is my formula.
=COUNTIF(F12:Q12, "*C*")+COUNTIF(F12:Q12, "*P*")+COUNTIF(F12:Q12, "*E*")+COUNTIF(F12:Q12, "*L*")+COUNTIF(F12:Q12, "*O*")
ii there a function that will count the number of different letters within a cell.
Example: If in cell A1 is LIVERPOOL then in cell B1 I want the number 7.
I have column A and B , in Column A cells i have words that I need to count the number of specific letters from them.
like :
A2= Apples
I need B2 to show the number of letter "A" in A2's text.
i have is Column A with groups of companys, each company takes up 3 rows, the second rows last 4 numbers are what i want the sort the groups on. as you can see there is no order to them at the moment.
so in theory if theres a company with the area code of 1000 their 3 rows would go to the top and company with aera code 1001 after that and so on.
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies View RelatedI have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
The formula in cell A1 of the attached inserts the worksheet name, but it doesn't work when pasted to a group of worksheets. The formula doesn't change from sheet to sheet, yet the results refer to the workheet name where the "paste" was originally done. Does anyone know of a similar formula that would work right when applied once across a group of worksheets?
View 2 Replies View RelatedI am trying to paste data into a sheet immediately following the last weeks data. I went to use an offset function like :
Range("A2").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
However, each weeks data is "Grouped" and therefore the .end(xldown) only takes me to the bottom of the visible rows, and the offset function selects one of the hidden cells.
I have a database to which I am connecting a form, I need to have a unique ID for each record and due to the number of tables need to include letters in my numbering (ex. U00001, for Users; B00001, for Books; and V00001, for videos.) I have adjusted a very helpful macro I recieved from Roy Cox and am currently trying this code on the "user form":
Count_Row = 1
DATABASE_RECORDS = Sheets("Users"). Range("B1:B10000")
'To identify the next blank row in the database sheet
For Each DBRECORD In DATABASE_RECORDS
If DBRECORD <> "" Then Count_Row = Count_Row + 1
RowNum = Count_Row
X = RowNum - 1
Sheets("Users").Range("A1" & RowNum) = "U000" & X
Next DBRECORD
This is supposed to find the fill the "A" (ID) column after the "B" Column has been filled. Currently It is placing U0001 in cell A12 when all that is in the sheet is headers.
I am trying to get the data out of a cell and put it in a textbox in my userform.
What I have is a Worksheet that has autofilter on. After the user clicks certain objectbuttons, there is only one row, that has data in it, displayed. The cell I'm after will always be in column A and be the second visible row.
I have the following in Column A. 01. Artist - Title
I manage to put 01 in Column B and Title in Column D
I am struggling to split between the "." and the "-" to show Artist in Column C
I am using in Column B
I need to find a formula that will find letters in a referance, for example i have referances like - MNE DJM & ZZPAR i need to find a formula that will find me the ZZPAR looking for "ZZ" i then want this to tell me what tpYe of referance number it is and put this into column Z.
Normal referance number like DJM and MNE i want this to show as "BROMLEY"
aND ZZPAR as "Chester"
I have tried something like the following but this is not working
I am new in extracting data from excel .
There is a report that contains a various amount data, in one cell it describes the outcome in a summary format of how an issue was resolved.
Is it possible to search a cell of summary text that contains a set pattern with letters & numbers i.e."CL900962" then either place a "YES" or "NO" in another cell if found?
The pattern will always begin with letters "CL" followed by 6 digit number.
The content of Cell A1 looks like this attccggttaattcccccaaaattt
(only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
Nice to meet you all. I'd be grateful for any help I could get on this as I have tried it and I'm a bit stumped...
What I need to do is the following:
Convert a 4 digit number (e.g. 1234) in a single cell to a 4 letter string (e.g BCDE) and have the output appear in another cell.
The conversion should be as follows:
0=A
1=B
2=C
3=D
4=E
5=F
6=G
7=H
8=I
9=J
0=K
So, for example, 3678 in one cell should be converted to DGHI in the target cell.
I am currently struggling with a spreadsheet that has been created in Excel 2007. Essentially, it has a number of items (individually identified by "S code" in the first column) that need to be tested at the specific dates over a one year period (i.e. at "2 weeks", "4 weeks", "8 weeks", etc) as shown in the screenshot below.
A user manually enters "Complete" into the corresponding cell in the "In-testing status" section of the spreadsheet when testing has been completed for a certain item at a particular time point.
Screenshot.jpg
I already have set up conditional formatting that highlights cells with dates older than the current date red. What I need to do now is to check for a particular item and date whether or not the corresponding "In-testing Status" cell reads "COMPLETE". If it does, I need to use a conditional formatting rule to return formatting to normal.
What I am unsure of is how exactly to retrieve the value of the corresponding "In-testing Status" cell.
Unfortunately I can't use a macro-enabled workbook in this environment
I have been trying to create a formula that will save me DAYS of messing around at work.
What I am trying to achieve is to have a sequence of numbers as follows:
BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.
This needs to be repeated for all letters to BNA01I09.
Then this sequence needs to be repeated to BNA12.
The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09
i want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:
before:
AA
AAA
A
AAAAAA
AAAA
AAAAAAAAAAA
I want it to look like:
AAAAAAAAAAA
AAAAAA
AAAA
AAA
AA
A
Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.
How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.
Is it possible to add letters to this "Unique random number" generator it is very fast and takes only 5s to run on 50,000 rows, I have a different "Unique random Sequence generator" macro but it takes about 30 minutes to run on 50,000 rows
Code:
Sub generateuniquerandom()
Dim b() As Boolean, e As Range, k&, x&
Dim lRow As Long
With Sheets(1)
lRow = .Range("F" & .Rows.Count).End(xlUp).Row
[Code]...
I am having slight trouble with the formatting of some cells. Every month I will be copying data into a row, the problem is Excel thinks it's a date instead of an integer of some sort.
The value copied will look something like this:
79:42:00
(meaning 79 hours and 42 minutes)
Excel is taking that and interpreting it as:
1/3/1900 7:42:00 AM
Is there an easy way to format this so Excel thinks it's a number (even if 79.7).
I tried to change the format of the cell to "Text", but it changes it to: 3.32083333333333
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less)
b) Present 3.333 as 3.33
c) Present 3.33 as 3.33
d) Present 3.3 as 3.3
e) Present 3 as 3 (NO zeros and NO decimal dot)
I'm working with Loan amounts and i need to know how to set a excel spread sheet to conditionally format a cell that causes a sum formula to exceed a preset limit.
Example ...
I believe Range.Numbeformat is the property.
I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.
I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"
The following is not working for me:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DistractorText As String
Select Case Target.Address
Case "$B$23"
If Target.Value = "Distractor" Then
DistractorText = InputBox("Type of Distractor:")
Target.NumberFormat = "@" & " - " & DistractorText
There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.
Working on matching 2 column of numbers.
Unable to match as 1 column has format where number has a space in front of first character.
_425638 as opposed to
425638
Not an underscore tough
Have tried text to columns - have tried editing cell and replacing space with nothing via edit/replace but no joy only way appears to be entering cell and deleting manually - there are thousands to update.
i have the next formula
=IF(C6="PRODUCT 1","$"&VLOOKUP(B6,N:T,3,FALSE)&" & "&"$"&VLOOKUP(B6,N:T,5,FALSE),"N/A”)
which aberrantly returns text:
$24.675 & $26.2
I was wandering is there a way to format or put a formula which will format the numbers to be with two decimal places. The cell containing the formula is already formatted as number and the source tables are numbers with two decimal places.
i want the result to look like this
$24.68 & $26.20