Which works great for values in the reference cell that do not contain carriage returns, which is possible. The problem I am running into is that if the reference cell contains a carriage return the destination cell just ignores it and crams the lines together for display purposes. Is there a way/formula I can use to force excel to display 'exactly' as entered, in cell returns and all?
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 isnt 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 doesnt have anything in it then dont 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.
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.
I have a situation where I have source data formatted like this:
Document ID Document Name Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
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.
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.
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!)..?
I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.
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).
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.
Is there a way to delete excessive carriage returns at the beginning of data. I pull reports from a SharePoint list where submitter entries often often contain multiple carriage returns before the text or data. I use ASAP utlitily to clean out excessive spaces before and after data, but I don't find a way to remove these multiple carriage returns so that the text entries are easily viewable when row-height is less than gigantic. Cells with extra carriage returns before the text entries end up looking like the following: ___________________________________________________________________ | | | | | | | | | Product page for CQ1-14047.LA is missing Swindex section (Core Drivers). | |__________________________________________________________________|
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.
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1 Price 2 Price 3FruitDeliciousPearStore 1 Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.
A prepopulated worksheet has the text "gold" entered in cell T278.
1. user searches for "yellow_metal" 2. Excell finds "yellow_metal" in row 278, say in cell A278. 3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example. 4. Excell returns "gold"
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.
I'm having some trouble figuring this out, although it's probably an easy fix. I'm trying to create a macro that when run, will enter text specified in the code in a cell of my choosing, and enter other information at the end of the previously entered text pulled from a different cell. I've attached at example of what I'm trying to do.
I have multiple sheets with the names Payrolldata_Companyname (the company name is different for each sheet)
On a sheet called EmailList i have a list of the company names. Part of my macro is giving the cell containing the company im working on a Named Range of CompanyName. For example i might have company in the list called ExcelForum, which is in Cell A12. Cell A12 has been named CompanyName.
I want my macro to select the Sheet called PayrollData_ExcelForum, by getting the ExcelForum part of the sheet name by referencing the Range CompanyName.
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?
I'm using an Excel spreadsheet as a database (mostly text based), so that in columns 1 and 2 are names and in column 3 is notes. I'm keeping all the columns single height (ie: not wrapping text). Where there is nothing in column 4 onwards, the text from column 3 displays across the subsequent blank fields.
How would I group and count the occurences of specific words in a text field? This is my first step in trying to develop a scoring method based on our notes.
If the sentences above were in the text field, I'd expect the words "a" and "in" to come back with a count of two while all other words would come back with a count of 1.
I would like to create a very simple Excel file that makes the following:
If I enter 2 in a field I want that 2 replaced with "07.15-19.00" and also a 1 entered to the field next to it. If I enter a 4 I want the 4 replaced with 00:00-08:00 and the number 8 entered in the field next to it.
I'm using SQL to retrieve a bunch of data from another Excel file. However, SQL only returns numbers, leaving text records with blanks (""). This only occurs in one column, which has numbers and alphanumeric values.
I use Vlookup all the time but this is one has be stumped. On the attached spreadsheet I trying to lookup the description on sheet 2 (lookup data) and return it to description on sheet 1 (orig data). I have the both fields formatted as text so I'm not sure why it's not returning the value?
I have a file from PubMed with a bunch of citations in it, which often go above 10,000. There are fields that mean certain things and I would like to be able to import the text file into Excel and parse certain fields into columns.
The text file looks like this:
PMID- 19782657 OWN - NLM STAT- In-Process DA - 20091109 IS - 1872-6240 (Electronic) VI - 1303 .......
