I have the following IF function in a spreadsheet. =IF(AE2 = "AZ", "We've recently changed our name from Company A to Company B.", "")
How do you Make it so there is a Space at the end of the period?
I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.
View 2 Replies View Relatedusing this format *.#,##0_);*.(#,##0);*.0_);@*. I get this:
Bonds..................................
would like custom format to do this,
Bonds . . . . . . . .
note:@ sign activates text formatting, *=repeats .=character being repeated
I've tried putting space characters before and after the period - no luck
I am trying to get periods added to initials.
Colum A
(Can be up to 4 caracters)
FJM
ML
B
GHTJ
What I need in colum B
F.J.M.
M.L.
B.
G.H.T.J.
What formula can I use in the B cells to get this result?
I'm trying to do a very simple web query drawing a table of holidays based on what country you select.
I'm using this very basic parameter setting
WEB
1
http://["Web Address"]
Selection=EntirePage
Formatting=All
[Code]....
I set up the connection and when I add the country (using a separate cell and a concatenate) the URL looks ok in the cell (www.timeanddate.com/holidays/Ireland) but the query returns an error saying it it's unable to open "www.timeanddate.com/holidays/Ireland."
I have a small range that i need to copy to another sheet for each working day of a time period(01/01/2014-30/04/2014) excluding weekends and holidays, adding the date in the first column of the new sheet.
View 9 Replies View RelatedI need to add spaces to a string of characters so that it is readable, the database is huge so I cannot do it manually:
For example,
123456John Doe 3456Adam Sharp I need it to apperar as:
123456 John Doe 3456 Adam Sharp
but still remains at the same column. Is there a quick way to do this ?
I was able to get rid of the weird character [] with the =CLEAN(E11), where E11 had the problem character. but now where the [] used to be between to words the 2 words just show as 1. Example If the bad cell was just[]test the clean cell has justtest without a space between the 2 words. Since the spreadsheet is about 8,000 cells and has random [] characters is it possible to not just clean the bad characters, but add a space where they used to be all in one command?
View 9 Replies View RelatedHow do I add a space between these 2 =Index functions
=INDEX(Customer!G$1:G$5,MATCH($C$35,Customer!$A$1:$A$5,0))&INDEX(Customer!H$1:H$5,MATCH($C$35,Customer!$A$1:$A$5,0))
As its displaying an address like this currently
22Newtonroad and I want 22 Newton Road
I have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
Is there a way to extend the space of my userform beyond its maximum space? I have tried using vertical scroll bars but they were of no use.
View 1 Replies View RelatedI have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?
I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.
String: Y60
~C CULT NUCLEUS 3X2 SPRING WST BK XL
Desired results: D60
CULT NUCLEUS 3X2 SPRING WST BK
I have two words of differing character lengths separated by a space.
How can I remove the first word... essentially, all the charcters to the left of the space AND the space itself?
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI have the following formula that works fine until someone uses the space bar to clear a cells contents
=COUNTA($D11:$AI11)
When the space bar is used to clear a cells contents the COUNTA statements includes the space in the count. How do I count the number of cells with content and exclude the space bar space in a cell?
If find dot. with out space in ( A1 ) cell remove space after dot in cell ( B1 ) Cell, vb or macro
A
B
M V Micunovic
MICUNOVIC,M V
L.T.Kudrjavceva
KUDRJAVCEVA,L. T.
D Sumarac m.l.
M. L,D SUMARAC
I only want to remove one space at the end of my text within a cell, if there is a space.
Code:
Sub hth()
Dim c As Range
For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp))
c.Value = Trim(c.Value)
Next c
End Sub
I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?
View 14 Replies View RelatedI have the following formula in a cell
=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1
that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1
So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08.
Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?
I have a bit of code someone on this board provided and I want to make it work for two different ranges. If I just paste it, I get an ambiguous name error. How do I make it work for a second range?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = [A2:A101]
If Intersect(Target, rng) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Target.Offset(0, 4).Value = ""
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 4).Value = Application.UserName
Target.Offset(0, 1).Value = Date
End If
End Sub
I want to add two times together.
14:00 (time) + 03:42 (duration) = 17:42
Is there vba code to be able to do this or an excel function?
I have tried searching but strangely couldnt find anything?
Below is my function.
="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I am trying to rank a list of numbers, such as:
1
3
5
3
4
1
I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete.
1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5
I have done more complex Excel formulas before, but for some reason this is stumping me.
If I have a sum function that adds up:
= SUM(U41:U45)
but I add a row at 41 I now get sum function
=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:
=SUM(U41:U46)
I have the below code of which I would like to addresses added to the CC list Also, Would it possible to add a range as the body? E.G:
.CCaddress = "somebody@mail.com"
.CCaddress ="somebody.else@mail.com"
Body text = Range("A1"& "" &"B1")
Code that need the above inputted:
With ActiveWorkbook
.SendMail Recipients:=Array("steve.howard@kuehne-nagel.com"), _
Subject:="610 Cambridge " & Format(Date, "yyyymmdd") & " " & "Counts"
[add cc address + body]
.Close SaveChanges:=False
End With
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
View 5 Replies View RelatedI am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00
January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:
=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)
The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.
With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).