Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Concatenate To Include Carriage Return


Name Address City State Number etc. I would like to concatenate these and include carriage returns so that the result looks like this:

Name
Address
City
State
Number

I'm currently using the following
#


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Carriage Return In A CONCATENATE Function
I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?

View Replies!   View Related
Concatenate With Carriage Return- How To Remove Blanks?
I'm currently using the following code to concatenate several cells into one cell, but it also displays the cells that contain blanks.

View Replies!   View Related
Concatenate Query; Include 2 Variables With 1 Constant Entry
I have recently discovered the concatenate formula. Is it possible to include 2 variables with 1 constant entry. i.e. I wish to establish file names with 2 variables, e.g. 0051 and 1025 followed by a file extension, .EC0 - the file name being 00511025.EC0. The variables are in Excel columns. I can achieve the wanted result by establishing an extra column and copying .EC0 into every cell of that column; but I would prefer not to have that extra column in the completed Excel file.

View Replies!   View Related
Carriage Return In VBA
I need to include over 70 data fields in a pivot table that I want to add/remove with a pivot. They need to have an alternative caption and be formatted.
I am currently using the following piece of
With ActiveSheet.PivotTables("PivotTable1").PivotFields("tytwprods_v_lytwprods_(abs)")
.Orientation = xlDataField
.NumberFormat = "#,##0"
.Function = xlSum
.Caption = "Products 2007-08"

However, I need to do this many times and it is a bit painful. I have created a spreadsheet with concatenated fields to create the above, although on one line which isn't liked by VB.

How can I make my code work but with it all on one line like:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("tytwprods_v_lytwprods_(abs)").Orientation = xlDataField .NumberFormat = "#,##0" .Function = xlSum .Caption = "Products 2007-08"

View Replies!   View Related
Carriage Return - Chr(10)
not compatible with older versions of Excel? When I try and open my workbook on an older version (2000 and below) I get a runtime error and it highlights this. What's weird is that even when I open the it at home I get this and I have 2003 at home.

View Replies!   View Related
Find Replace With A Carriage Return Using VB
Selection.Replace What:="GD", Replacement:="BLACK", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="WD", Replacement:="WHITE", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _
False, ReplaceFormat:=False
GD and WD are in the same cell, I would like the end result to go from GD WD to:

BLACK
WHITE


View Replies!   View Related
Deleting Text After Carriage Return
A cell contains the name, tel number and address of a user. Those 3 fields are separated by a carriage return (alt+enter) in the cell. I need a macro to keep only the name, which is on the first line of the cell. The macro should be generic and should work for any particular name.

John Smith
96783456
Hollywood Street

View Replies!   View Related
Combining Cells Into One, But Seperated By Carriage Return
I have rows of data in one column. I want to combine them all into one cell and have each row on a new line.

I've tried =A1 & char(10) & A2, (and a bunch of other char(#s) for carriage return) but it just shows an open box in place of the char().


View Replies!   View Related
VBA To Insert Carriage Return After Certain Characters
Is there any way to insert a carriage return after each instance of a character? I have a section of data that requires a carriage return after each instance of -v eg:

View Replies!   View Related
Coding The Difference Between A Manual Carriage Return And VbCR
I'm trying to use the 'speaking cells' utility. Unfortunately, it seems to require a manual carriage return - I have tried offsetting and I have tried this (below), but to no avail:

Sub speak()
Range("A1") = "YES"
ActiveCell = vbCr
End Sub

View Replies!   View Related
Macro - Add Carriage Return To End Of Text In Cell
I am looking for some assistance in creating a macro that would allow me with one key stroke to enter a cell in edit mode, add a carriage return to the end of text in the cell, then move the cursor to the next cell.

View Replies!   View Related
Replace Line Feed With Carriage Return In Text File
If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content.

Instead, if I select directly the content from the formula bar, it isn't happen.

Do exist a way for copy and paste directly from the cell without select from the formula bar?

Maybe with a macro?

View Replies!   View Related
Concatenate Text With Soft Return
I would like to concatenate text comments from 14 worksheets and place a soft return between each next set of text. To further complicate, not all of the worksheets will have comments.

View Replies!   View Related
Incorporate Concatenate Funtion: Return Text Rather Than A '0'
I have the following formula: ...................

and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how.

I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "-". If not, then run through the original again (I pasted the original again after the "-").

View Replies!   View Related
Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View Replies!   View Related
How Do I Get Rid Of Carriage Returns?
When I save my file as text and upload into a program, I get a response saying that my file can't be processed due to carriage returns. What are these things and how do I get rid of them? My excel file has about 6000 rows and 50 columns of data.

View Replies!   View Related
How To Force Carriage Returns
I am concatenating data which will be displayed in a text box.
Is there any way to force a carriage return within the formula ?

View Replies!   View Related
Remove Trailing Carriage Returns
I have an SQL query that returns some text data to a cell from another system.
In this system the users sometimes enter superfluous carriage returns after the text.

Can someone tell me how to write a formula/VBA code that would remove the trailing carriage return/s.


View Replies!   View Related
Extract Data Between Carriage Returns
I have a cell that contains several carriage returns. How do I extract data from between 2 carriage returns?

View Replies!   View Related
Search On Carriage Returns/characters
I have done a search on carriage returns/characters and what has been suggested is not working. I have BOTH carriage returns in the cell and also the boxes (that I assume are also carriage returns. I tried the substitute formula (=substitute(cellref,char(10),)) in excel but it only removes the alt+enter carriage return. I tried the various suggestions of find & replace but the chr$..etc did not work! Is there a formula to clear both in one (not a macro!)..?

View Replies!   View Related
Combine Text With Carriage Returns
I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text. The cells are arranged like this:

A1="One"B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"

I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=””,””,A1)&IF(A2=””,””,CHAR(10)&A2)&IF(A3=””,””,CHAR(10)&A3)&IF(A4=””,””,CHAR(10)&A4)

The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text. Is there a way to change the formula so that it will do the following three things:

1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.

View Replies!   View Related
Macro To Remove Carriage Returns From Worksheet
I need a macro that runs through the cells on a sheet and removes all carriage returns. The carriage returns appear as small squares amongst the text (they come from a CSV file).

View Replies!   View Related
How To Remove Square Carriage Returns From My Cells
I have a worksheet containing square carriage return symbols (see below).

EXAMPLES
Eg1. Baked[]Beans on []Toast
Eg2. Smoked[][]Salmon in Brine
Eg3. Ice[][][] Cream [][]Cosmopolitan
Eg4. Mixed[]Nuts[][]per kilo[][][][]
Eg5. [][]Baby Shampoo[]Fragrance Free

This data was extracted from an SQL Database and dumped into Excel.

I would like to know if someone can suggest a script to replace all occurrences of [] with a space.

Once I perform this, I can then perform a Search/Replace function as follows:

Replace 4 spaces with 1 Space
Replace 3 spaces with 1 Space
Replace 2 spaces with 1 Space

Hopefully then, the final result will appear as follows:

Eg1. Baked Beans on Toast
Eg2. Smoked Salmon in Brine
Eg3. Ice Cream Cosmopolitan
Eg4. Mixed Nuts per kilo
Eg5. Baby Shampoo Fragrance Free

Eg5. is a little tricky because a space will appear at the beginning of the cell (see above). It would be good if there's a script to remove occurrences of this also.

View Replies!   View Related
Replace Double Carriage (returns) With Single
Is it possible to replace double returns with single?

View Replies!   View Related
Replacing Carriage Returns With Delimiter Within A Cell
I need some direction on how to proceed with dismantling an address cell. I have a workbook that one field is an address label field. What I mean is the entire address is entered in one field just as it would appear on an envelope. I need some suggestions on how to break this apart and create individual fields for the individual parts.

View Replies!   View Related
And Formula To Include 0
At the moment I am using the formula

=IF(AND(B3>C3,D3,E3,F3),"eat less","eat more")

I want it to show me that if B3 is bigger than any of the other cells that you should "eat less". It works if a number is entered into every cell, but if a 0 is entered into one of them it comes up with "eat more" when it should be "eat less"

View Replies!   View Related
Include Saturdays And Maybe Sundays
currently I'm working as project manage for a software development studio and I am using MS project for all my work. Since the development team aren't aloud to have a MS project license on their PC I have to copy their tasks from MS project and put them into Excel.

I would say that I am okay using Excel but not an expert. I want to create a formula that uses NETWORKDAYS to determines a finish date from a duration and start date.

The headers I would use for each column are Duration, Start and Finish. So I would like to be able to get a team member to put a duration down i.e 1 day and give me the start day and in the finish date it produces a finish date automatically without including Bank Holidays and Weekends. What would the formula be for that?

I have the same question as Madball and was able to work out the Worday function, however for my company, we work on Saturdays and or Sundays sometimes.

I would like to be able to specify from line to line if Saturday or Sunday needs to be included in the duration calculation resulting in a finish date, however if I can simply get one formula to work that includes Saturdays alone (most common working day of the weekend, and do work more than don't) it would be the most helpful, I think I can get it adjusted from there.

View Replies!   View Related
Summing Figures Which Include #N/A
how can I total three columns together, one with the value of #N/A, and two with figures in, without the total coming back as #N/A ?

View Replies!   View Related
Include Two Conditions Into IF Statement
I am trying to include two conditions into this IF statement.

=IF(ISBLANK('SHEET1'!W8),0,1)

I want to include the cell X8 in this aswell.

View Replies!   View Related
Include That Weekends Are Free
Does anyone knows how to calculate if translator is free or not, regarding that weekends are free ( I mean if weekends are free how to check if translator is able to translate a document)

View Replies!   View Related
SUMPRODUCT To Include More Than One Month
In the following formula I'm looking for MONTH 1 (January) and WEEKDAY 1 (Sunday):
=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$6936)=1),--(WEEKDAY(Sheet1!$A$2:$A$6936)=1),--(Sheet1!$D$2:$D$6936=$D2),Sheet1!E$2:E$6936)

is it possible to find MONTH 1 and 2 (Janauary and February) and use the rest of the formula as is? Can the same thing be done looking for MONTHs 3,4,5 or months 8,9,10,and 11?

View Replies!   View Related
Include Vlookup Within If Statement
How can you return a Vlookup as a true value in an If statement? For example

View Replies!   View Related
Formula To Include A Match To Any 8 Numbers
I have a formula to pick up certain bits of information depending on the contents of a cell.

=IF($F3="138-1",MID($E3,11,4),IF($F3="151-26",MID($E3,11,4),IF(MID($F3,1,2)="00",$E3,IF(MID($F3,1,2)="40",$E3,IF(MID($F3,1,1)="-",$E3,IF(MID($F3,1,1)="8",$E3,$F3))))))
The data in column F has a mixture of 8 digit numbers including numbers starting with multiple zero's, the numbers 138-1 and 151-26, then some text.

Orginally the 8 digit numbers only started 00 so it was easy to pull out the relevent information, then they started 40 and then 8. I'm just worried that I'm going to end up with too many nested 'if' statements (and reach the max limit), so I was wondering if I could do 'any 8 numeric digits' instead of this current 'dead end' solution.

I'm not sure if I could use a wildcard of some sort with the MID formula

View Replies!   View Related
Converting Figures To Include Miliseconds
A colleague at work is trying to create a spreadsheet which subtracts the time from one column to the other to provide him with the difference

the first question is how do you get excel to provide you with the format for milliseconds
eg if i input 050750600 im looking for excel to return 050750.600 which represents
5hrs7mins50secs.600milliseconds.

I have tried using the custom function without any luck so fa

View Replies!   View Related
Change Array To Include New Cells
I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?

View Replies!   View Related
Calculating Stdev When Values Include #n/a
I have a set of values. #N/A413636363632. The #n/a is there on purpose for charting reasons. How do I calculate the stdev of that range? i.e. ignoring the error?

View Replies!   View Related
Look Up And Match Several Values To Include Entry
I want to be able to look up and match several values in my worksheet to confirm, that an entry is valid. I would like the test to return an OK / NOT_OK value at the end of each entry. In the attached file you will find four different variables. In order to include an entry, it must be matched to find related entries, before it can be included.

For an entry to be OK, it must contain a company that has been listed in the same industry, in four different quarters (not necessarily in the same year). A company can be listed in several industries, but as long as there are not four entries in the same industry in four different quarters, the entries are not yet OK (hence NOT_OK). Therefore the only entries that are valid in the attached file are:

Company A Q1 2009 Administration OK
Company A Q2 2009 Administration OK
Company A Q3 2009 Administration OK
Company A Q4 2009 Administration OK

I work in XL 2007, but have converted the attachment to 2003 for maximum compatibility.

View Replies!   View Related
Include Cell Value In Message Box
I have the below macro designed to display a message box if a cell in the range specified is greater than the value in the designated cell (BF7). In the message, I'd like to include the value for the cell in another column, from the same row as the cell that met the condition.

For example:
Cell E11.........Cell BF11........Cell BF7
Mark....................1.......................0

What I want the message box to say: Mark worked too many days.

I understand how to include a cell value like so:

View Replies!   View Related
Formulas That Include Blank Cells
I can't figure out how to make this formula work because I need to use cell AG46 and it contains an IF formula that leaves the cell blank if the sum = 0. I keep getting the #VALUE! error. How do you create a formula using a cell that has an IF formula that could leave the cell blank?

Basically this is what I need in cell I12:
=sum(a12)-(ag46*8)

View Replies!   View Related
Include The Dropdownlist In VLOOKUP Formula
i want to include the dropdownlist to VLOOKUP formula. See the attachment

View Replies!   View Related
How Do You Include Specific Cells In The Email
I have the code to send a email based on the due date. The email will include the Due date for the purpose below of today 1/14/08 in the message. I cannot figure out how to use Offset in the email message portion to include

The Last and First names along with the Exp Date in the respective column.

Example below: row 3--due date shows up in email message, what would the Offset code be to include Grimm, Paul, Exp Date3, 1/14/08 in the message?

Then the same for row 2, the due date 1/14/08 which the Offset information would be different than line 3?

Since the due date can be in various places how do i still get the Last First and Exp Date"x" included in the message of the email? ....

View Replies!   View Related
Getting Autofill To Not Include Weekend Dates
1 have figured out how to create an auto fill that is a mix of days in between. (When dates are not in a row.) For example, a schedule that starts a date, and then adds 2 days, then 1 day, then 3 days etc....it is for a tracking schedule. Once created those amounts are the same for for every row. The problem is, when it counts the next day, or 2 days or 3 days etc, it cannot include a weekend date, rather just 2 working days, or 3 working days etc.
Is this possible?

Right now, I start my first cell with a date, and go to the cell to the right, and in my formula, I type the previous cell address and I add (+) some value (2) to represent the day amount, but right now it includes the weekend days as well, and I need them skipped.

View Replies!   View Related
To Include Lots Of Text In A Cell
I'm attempting to use Excel as a writing tool.. it's incredibly powerful as it will allow me to customize my workspace, create links, flow charts etc... but an area I'm most concerned of is at the heart of this endeavor, which is text... lots of text!

I believe there is a limitation regarding the amount of text within a cell ( something like 250 characters?) Is there a way to customize/format a cell to include much more text?

What route would you advise? ..Forms, pivot tables, textboxes?

View Replies!   View Related
Include Blank Value In Validation List
i have two sheets in workbook named as shtbiz,shtbiz1.

so column A of my shtbiz1 sheet i have 100 datas.the by using following code i can fill the datas of shtbiz1 in column A of shtbiz.

Dim strValItems
strValItems = ""
Set rngValList = ShtBiz1.Range("A6", Range("A10000").End(xlUp))
With ShtBiz.Range("A6:A10000")
. Validation.Delete
For Each rngValCell In rngValList
If Len(strValItems) > 0 Then strValItems = strValItems & ","
strValItems = strValItems & rngValCell.Value

Next
.Validation.Add xlValidateList, , , strValItems
End With

so my problem is i need to add blank value in the top of the list .ie first value should be blank then other values which read from shtbiz1 has to be followed.

View Replies!   View Related
Include XML Files In Workbook
My Excel VBA application needs to communicate with 2 XML files. Instead of compressing these XMLs with the Excel file in a Zip file, and send them to the client, I would like to include them inside the Excel file.

This way, I will only send the .xls file (the 2 XMLs will be included inside).

View Replies!   View Related
Sum Across Worksheets To Include New Sheets
A colleague of mine maintains a spreadsheet with a worksheet of data for each month labeled "Apr'07", "May'07", "Jun'07", etc. He then has a summary worksheet that gives summaries of the various data fields on the monthly sheets. Currently he has been using the kludgy method of listing each cell to be summed. (e.g. " SUM('Apr'07'!A2,'May'07'!A2,etc.) Since he is dealing with a summary of about 200 rows by 15 columns this is a big pain to maintain. My first thought was to set up his formulas as a range of sheets rather than naming each; E.g. SUM('Apr''07:Jun''07'!A1)

Then I thought that rather than having to update each formula when he adds a new month, he would be better off having two cells that control the start and end sheet and having then feeding them into the summary formulas using the INDIRECT function.
However, try as I might, I can't seem to get the INDIRECT formula to work. In the attached example spreadsheet I have set up B1 and B2 to hold the names of the start and end worksheets, respectively. So my attempt at an Indirect Sum formula is the following: SUM(INDIRECT(" ' "&B1&":"&B2&" ' !A1"))

(Note: spaces added between " and ' for clarity) When I look at the formula evaluator it evaluates to SUM(INDIRECT("'Apr''07:Jun''07'!A1") and then generates a #REF error. What am I doing wrong here? I'm open to all suggestions of fixes to this method or an alternate method that would achieve the objective of performing calculations on cells across a dynamic range of worksheets.

View Replies!   View Related
Include A Message Within Vba Code?
I would like to include a message within this VBA code, not sure of the code to use.

ActiveSheet.SendMail recipients:="dan@aol.com", _
subject:="Your inputs are due", _
returnreceipt:=True
End If

View Replies!   View Related
Pivot Table Refresh To Include New Data
I have an existing pivot table that is feeding from data in another sheet.

The data gets updated every month and sometimes their are more columns of data than the previous month. Everytime there are more columns they are not added into the pivot table when i refresh the table even though they are included in the range.

Is there are way the pivot table will add these new columns when i refresh the table without me having to manually drag them in?

View Replies!   View Related
Modify Sumproduct To Include Helper Column
I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column.

If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),1,0).......

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved