I have the following forumla....
=SUM(B11+E11)/F11
in those three cells there is a value of 0
the following message appears in the formula cell - #DIV/0!
is there a way to change my formula so it doesn't display this message and just return 0
cell a1 has yes cell b1 has yes and i want to type yes in c1 that will change a1 and b1 automatically to a blank cell
View 3 Replies View RelatedMy data base downloads with signs $,#, etc
How can I remove these from a cell and leave the numbers
example
A1 102,462#
A2 83#
A3 4,265#
I need to do a lookup on these cells but for some reason they all appear to have some kind of space characters in each cell which you can see if you double click on them. I have tried trims, text to columns, find replaces and none of these seem to work
I have a huge list of these numbers / codes and they all have the weird space kind of characters at the end
Can anyone give me a solution on how to remove them so my lookup will work?
I have spreadsheet that was sent to me but for some reason column A-D do not have the grey grid lines. They have the row lines but not the column lines. The data fits into column A but when I click on Center it all disappears and and I have to open the cell up nearly half the page to view the data again.
I have attached a screen shot of the problem. What I need to know is how to undo that so that it has normal formatting like a new spreadsheet. excell.jpg
I currently am pasting data that in some cases there are numbers at the end of a list of names e.g Andy Cole10 or James Banks3.
I have used a formula before that seperates the numbers into another cell but now i need only the text and not the number.using A & B column as example and i can adjust the range.
remove numbers from a cell.For example cell value is Drienne Kiop C0411377 or Tsler, Hopkin & Hart LLC B0002503
I am looking for a formula or function to get result ]Drienne Kiop or Tsler, Hopkin & Hart LLC
See the attached file. I regularly receive input files which I have to reformat and upload to a website as a .csv. The website does not like the / between the characters on the Input Data tab. Is there a way to automatically search and remove them and leave the rest of the characters intact? The columns do not always appear in the same order and there could be more or less columns. On the Import Template tab, columns A-I will always be there, so ideally the code should make the changes from the Input Data tab and copy the present columns (J thru whatever) header and data over to the Import Template tab starting in 'J1'. I think that VBA would be the best way to go due to the variable nature of the columns.
View 4 Replies View RelatedI have some code that display date and time in a cell when "done" is input into the adjaecent cell. (Any value would be the trigger, though)
View 2 Replies View RelatedI have 2 columns of data which contains mainly numbers that are either 9 or 10 digits long. However some have got so words at he end which is what I want to remove. I thought of using text to colums but there is no space or symbol between the number and text.
An example is
0111111100ddddfffffddd
or
0222222222ddddd ffffff ddddd
I have the following in cell A1:
0113 CDW 3 yr Coterm - Ironport devices
I need to remove 0113 from this cell to read only:
CDW 3 yr Coterm - Ironport devices
Any formula to do this?
removing certain words/charactors in a cell.
For example, I have these information in 1 excel cell.
"Company allows staff to purchase small value items (items amounting to less than $3000) with approval from the staff's director."
So in this case, I need to remove the HTML/XML tags manually. Therefore, the final result should be,
"Company allows staff to purchase small value items (items amounting to less than $3000) with approval from the staff's director."
It is very tedious with the huge amount of data to process.
I have a column with 11 digit numbers formatted as text (Column C) and it seems like there's three spaces after the number (In Sheet A). I am trying to do a vlookup comparing Sheet A to Sheet B. It's not working and not sure if it's the extra spacing in the cell of Sheet A that's causing the Vlookup not to work.
I would like to compare both lists to see what UPC's are on both sheets. Any ideas how I can do this?
In Sheet A, Column C contains UPC (11 digits) 07845968952
In Sheet B, Column E contains UPC (11 digits)
I need some logic/code that will remove some characters.. If 2 or more br br
are followed by each other I want them deleted.
the br is supposed to be an html break but it won't let me put them in on this forum
I ran a couple of formulas in column E and now it looks like as shown in the attached image.
The cells now contain different characters at end. Sometimes its a couple of commas, hyphens, blank spaces etc.
Is there a formula which can remove the ending characters in case they are not alphabets or numbers?
find a formula or function where i can remove the last 4 digits from cells. I have a spreadsheet where there was might have been concatenation do where 4 digits were added to the end of part numbers in a cell. I need to remove just the last 4 digits so that I can do matches later on. Each cell has a different number of characters and i just need to remove the last for Example 154758NE20 or NFD148574DE11. Need to remove the NE20 and DE11 respectively.
View 6 Replies View RelatedAn externally generated CSV file gives us one column which shows a location, year and then individual names in brackets, like so:
London 2013 (Firstname Lastname)
Delhi 2012 (Firstname Lastname)
I'm wondering if it is possible to separate out the text before the parentheses, so that I can show "London 2012" (for example) in another cell. The number of characters changes from row to row, so using the LEFT and RIGHT functions doesn't quite do the job.
I found this template, it works great, I have customized it to fit my business. One thing I cannot seem to change is the cell background on the shift tab. I am able to change the background but on the schedule tab when I select a shift the background is still the same. Here is the template [URL]
View 9 Replies View RelatedI have Main worksheet (sheet 9) and some codes in Sheet 10 which I use as a source for data validation in Sheet 9. So far I have a macro which looks for changes in cells and, when someone chooses a code from the list the macro adds a comment to the cell and populates it with an explanation of the code. The explanations are also in the code worksheet (Sheet 10). It works really nicely but I cannot work out how to remove the comment if the user clears the value in the cell.
My Macro thus far is:
[Code] .....
I am trying to split cells that have addresses in them into separate cells via writing a formula but am having no joy, i wonder if anyone could help me please.
What i need is to split cell A1 which contains an address i.e
Red Roofs
12 Chiltern walk
Tunbridge Wells
Kent
TN1 1PL
England
I need to put a formula in cells B1,C1,D1,E1,F1 and G1 tha splits each line of the address and puts it in the separate cells so it would look like:
B1 Red Roofs
C1 12 Chiltern walk
D1 Tunbridge Wells
E1 Kent
F1 Tn1 1pl
G1 England
I have a limited amount of VBA knowledge. My limited knowledge is based on solving my problems by looking at the answers already on this site and trying to adapt it to my needs. Here goes:
I import a list of address and telephone numbers (100+) into excel. example - Cell A1 contains "10 downing street, whitehall, london, SW1A 2AA 01234 567890", sometimes there is another space after the number, sometimes there is no telephone number. I want to remove the telephone number at the end if it is there. Have searched the forums have have come up with the follow solution:
1. import list to column A
2. copy and paste Trim to column B
3. copy values in column B and paste to column A
4. delete column B
5. remove the end numbers
6. copy and paste Trim to column B
7. copy values in column B and paste to column A
8. delete column B
9. remove the end numbers
It looks like this in code:
Sub testa()
'select (=TRIM(A1)) formula and paste to sheet
Sheets("Formulas").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Columns("B:B").Select
ActiveSheet.Paste
[Code]...
'copy values of column B to Column A and delete column B
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
[Code]...
'copy values of column B to Column A and delete column B
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub
I know this is a long winded way around (still chuffed I worked it out by myself though!). If I merge all the macros into one marco it continually loops and deletes everything in the cells and I have to press escape to stop it.
My questions are:
1. Why, when I merge all the macros doesnt it work the same as individual macros? (I would like to know for future reference).
2. Its there a better way to remove the telephone numbers?
I am working on a form right now for a shipping company in MS Excel. Anyways, I have a few cells formatted as text that have a starting value of "[Insert Comments Here]". What I am trying to do is have it so that IF the cell has the original value "[Insert Comments Here]" upon printing, THEN the cell's value is changed to "" or some other way of not making it print.
I would like to be able to do this both with cells and the "Label" form objects.
I have 40,000 part numbers that range in style.
For example:
13538-AL
BC-DIB-13
13750U
13762-1
I want to take all the numbers that have the ending tag "-XX" that are LETTERED but NOT the numbered combo (just like example 13538-AL) and remove the tag to just have the root 5-digit number.
I have all the end tag combos I would like removed. SO if I needed -AL, -LG, -CG, -SS, etc. removed, I would need the formula that would find all the 5-digit numbers with those end tags and remove those tags, leaving the other numbers alone.
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
where i copy up to 1000 products ID's like this: "1234567890 AA11111"(ofcourse there is allways differend code for every product its just a example :)) and paste it to exel, and the problem is i need only the 10-digit number (it's allways 10-digit number but the second one is sometimes diffrent) so it is possible to make makro that will remove every character after 10th character ?
View 6 Replies View RelatedI need to remove the letter within the middle of a persons name.
For example, if the person is called 'Michael J Fox' then it needs to be edited to show only 'Michael Fox', if the person is called 'George W Bush' then only 'George Bush' should be displayed etc. However the persons name should remain as is if a middle character initial does not exist.
VBA method to remove HMML tags from the contents of a cell?
It needs to remove all occurrences of the "" characters, plus anything that is found between a "< >" pair.
Is there a code that can be written to remove duplicates within a cell for an entire column?
View 1 Replies View RelatedHow to remove words that are repeated and adjacent within a cell? I have about 20,000 cells to do this with so the spellcheck is not an option.
View 9 Replies View Related I've got a whole load of cells that have a pipe ( | ) on the end of the value which I'm trying to remove.
Easy enough thinks me, so;
Code:
=LEFT(AB188,LEN(AB188)-1)
And then Copy / Paste Values Still has a pipe on the end... I check the cell for trailing spaces, there are none...
I check the length of the cell (658) against the length of the result from the formula (657)...
So, my formula works, but when I copy and paste as values the pipe remains.
I'm using Excel 2007 and the cell contatins other pipe characters I'd like to keep in there so I can't find and replace or anything.