is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.
View 2 RepliesI was wondering if there would be a way to use vlookup and still be able to type text in the same cell. For example, if the vlookup gives me a result of 123. I want it to also pull from a different cell that I've typed in 321. So the resulting cell would show 321-123 or 321 123. I have attached a sample sheet. On sheet two where the 3 items are, I want it to display 321-123 for item 1, 321-234 for item 2, and 321-345 for item 3. The 321 would be dependant on what I type into B1 on the first sheet.
View 2 Replies View RelatedI have a worksheet and in two of the cells I have text. In another cell I want to put a formula in so that if certain text is typed into each of the two cells a result will appear in the third cell.
Hazardous
Smiths
60%
Non Hazardous
Smiths
20%
Hazardous
Browns
45%
Can I put a formula in and if so, what formula would it be?
I need it to be like:
=B10 2014
(So the first part changes depending on what B10 is but the 2014 always stays)?
I am trying to pull titles out of a text string but its quite difficult because of how the data was originally inputted. I need titles that appear in the following different formats
" title"
title/
title"
These titles were not inputted consistently and it could show up in the middle or at the beginning. I have attached a sample of the data that i am working with and a sample formula that i was trying to use to obtain my information. The formula that i have is a nested formula of find and mid formula that searches for the text within " ".
I'm currently building a large spreadsheet which will require numeric codes to be within certain cells. At present these cells are filled with alphabetical codes and the person using them has little knowledge of the numbers, is there a simple way for them to type in the alphabetical code and it to then auto-replace it with the numberical?
View 8 Replies View RelatedI've got a single column worksheet with a varying numbers of characters in each row.
At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.
I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:
Dim lngSpacesNeeded As Long
For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i
Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".
I want to use the Validation function under the Data menu to check a cell for a number range, a blank cell, or for one or more spaces. I have a formula to do the first two but not to check for one or more spaces. I'm running Excel for Mac 2011.
View 5 Replies View RelatedIs there a way to see if a textbox is blank.. what I mean is, there may be some spaces in the text box which make it seem that there is something in the Textbox but all they are, are blank spaces.. no numbers in otherwords..
On a userform I have a textbox7 to enter in only the last 4 digits of the persons shop phone #. I have it coded that if the textbox isn't empty, it will put the required extension "X-" in front of it to identify it as a phone extension. If the cell is empty, it will remove it so it doesn't put the X- in the database without a phone #.
Finally the problem, if there are spaces in textbox7, no numbers, it will think that something is in there and put X- .. this messes up the database.
How do I check the textbox to see if it has numbers in there.. This is my code now...
' If TextBox7 is empty Then Phone = Blank
If TextBox7.Value = "" Then
PhoneC = " "
Else
' Preps Phone number with X- for extension
Fourdigit = TextBox7
Plus = "X-"
PhoneC = Plus + Fourdigit
End If
I am trying to have one sheet with company names on it and another as a schedule. I have about 20 companies that I need to list on the main schedule sheet. Is there anyway when I type a company name in one sheet, when it copies that cell to the schedule sheet, format that cell to a specified format? I know conditional formatting will work, but it only allows 3. I need around 20. I would just like to change the background color of that cell when the name is typed in.
View 9 Replies View Related=IF(D$21="SOUTH FLORIDA 6","6 MONTH FENCE RENTAL",
IF(AND(D$21="SOUTH FLORIDA 12",S15=24),"24 MONTH FENCE RENTAL",
IF(AND(D$21="SOUTH FLORIDA 12",S17=12),"12 MONTH FENCE RENEWAL",
IF(AND(D$21="SOUTH FLORIDA 6",S16=6),"6 MONTH FENCE RENEWAL",
IF(AND(D$21="SOUTH FLORIDA 12",S14=18),"18 MONTH FENCE RENTAL",
IF(D$21="SOUTH FLORIDA 12","12 MONTH FENCE RENTAL",
"BIKE BARRICADES ARE ALSO AVAILABLE"))))))
I having trouble getting this IF statement to function properly. The Bold section is the part that will not function properly. I have checked all the spelling, spaces, but for some reason it wont display properly.
Based on the above is there a way to make this a type of lookup?
Im at wits end with all the ifs.
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
In colmn A:A i need returned, which numbers (if any) are repeated and how often each repeated number occured.
Admitedly... I'm lost on this one.
I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
Is there a formula that can sum only when i am typing numbers on a cell, but only when i enter new data to double check my quantities.
View 5 Replies View RelatedSee the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...
Cumulative Input.xlsx
I need to identify repeated code with different text value. The codes in B column appears repeatedly in the list. However some codes have different text value (one time appears with public and another time appears with priv). I would like to identify those codes as 1 if they appear with different text value within the list. I highlighted one code 131775 for reference.
Repeated code with different text.xlsx
In my excel sheet i have few results displayed in range (same row different column with few banks cells in between). No i wish to display the most repeated value / result from that range into another cell on a different sheet of the same workbook. Can somebody help please.
Please note that the data (that is displayed in the cell rage from where i wish to get the most repeated one) is a the result of a nested IF statement.
16 minutes ago
i want a function that tell me how many duplicates are in the ID for a particular text.
View 9 Replies View RelatedCan I have a picture appear next to what is typed in the cell next to it. I have a drop down list of the different pictures I have, I want to select, for example, "star" from the drop down list, can I have a picture of the star appear in cell next to it? I have all the pictures of the words in my drop down list.
View 1 Replies View RelatedI have a list of drugs which possibly could increase to thousands in rows. I will need a macro that can scroll down to a particular row when the text in that row has been entered.
Assuming I have LOTEMP in A5000 , I would want a macro to scroll to row 5000 when the word , " LOTEMP" is typed in cell , say B2.
For all bank transactions I've downloaded, I would like to add a subject. The list contains many hundreds of rows, so I tried to make a macro for it. Unfortunately I cannot manage to search if a cell contains a certain text string. I came up to:
View 14 Replies View RelatedI'm trying to identify if text contained in one cell is also contained in another cell. I used the following formula with some success, but it is not successful in all situations, and I don't know why.
=IF(ISNUMBER(SEARCH(+A1,C1)),"yes","")
Attached is a spreadsheet with the example.
I want to set up a sheet macro that will replace the contents of a cell (when a specific number is typed in) with a specific formula.
For instance, when "43" is typed into any cell within specified columns (say columns C and H), it changes to the following formula: =IF(D47="",43,IF(D47<=3030,408,43))(WHERE D47 WOULD REPRESENT THE CELL ADJACENT TO THE CELL WHERE "43" WAS TYPED.)
IE:
If "43" was typed into cell C1, then it would change to the formula: =IF(D1="",43,IF(D1<=3030,408,43))
If "43" was typed into cell C50, then it would change to the formula: =IF(D50="",43,IF(D50<=3030,408,43))
I'm having a hard time figuring out a solution for this situation. By the way I'm working in VBA macros. I need to check a cell for two things: if its empty or if it has something besides numbers in it. I know how to code the rest of what I need, but I'm just stuck on identifying if the cell has something else besides numbers inside it.
Possible entries would be "Isn't an enhancement" or "NOT A SIR", but I can't be sure those are the only things so I just generally need to check if there are any letters in that cell or not. One thing to note is that the contents of the cells are in text format and not number (the cell has a little green arrow in the top left that tells me about it). Any help would be appreciated, thanks in advance! Oh, just remembered, the number has to be 5 numbers long so I thought of using the Len() function but that doesn't cover things that are words under 5 letters like "None".
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.
View 5 Replies View RelatedI'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
Sub RatingsFix1SP()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
For i = 0 To 3
For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 6) = 0.15
End If
Next rngCell
Next i
End Sub
To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
a1=aaa
a2=111
a3=222
a4=333
a5=bbb
a6=444
a7=555
a8=ccc
a9=909
a10=234
if a1 = aaa then copy a1 to b1 until the cell =bbb
if a5= bbb then copy a5 to c1 until the cell =ccc
if a8= ccc then copy a8 to d1 until the cell = ddd.........
I am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B,
else if the first letter occurs from H-M, display H-N in cell B
else if the first letter occurs from N-R, display N-R in cell B
else display S-Z in cell B.