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 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 9 Replies View RelatedI 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.
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 14 Replies View RelatedI am concatenating data which will be displayed in a text box.
Is there any way to force a carriage return within the formula ?
I have a cell that contains several carriage returns. How do I extract data from between 2 carriage returns?
View 5 Replies View RelatedI 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.
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 2 Replies View RelatedI'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.
View 9 Replies View RelatedIs it possible to replace double returns with single?
View 9 Replies View RelatedI 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 6 Replies View RelatedI am trying to link the data from one cell to another. My destination cell has the formula
[=IF('Request Form'!A39="","",'Request Form'!A39)].
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?
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 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:
Document ID
Document Name
Author
[Code]....
Now I have a decimal column and I would like to remove all of the trailing zeros. It is using the number format. How do I accomplish this?
View 10 Replies View RelatedI have a column of data in which each cell contains a line of text. Each line of text has spaces after the letters end and there doesn't seem to be a consistent number of trailing spaces.
I can clean up the data using the TRIM function but is it possible to use the TEXT TO COLUMNS functionality?
I have a set of data with trailing Y in certain cells.
Instead of the replace function in Excel, is there another way of removing the "Y" in that cell and storing it another cell while keeping the numbers before the Y.
i've created another column to determine which cells has Y as the right most character by using =IF(RIGHT(F2,1)="Y","Y","")
So for example, I want to remove the Y from say Cell A2 and store it in say cell A3 while still keeping the value 8624.46 in A2
Cell A2 = 8624.46Y
I have an excel file with two sheets . One containing the updated prices with its code ( Sheet1) and the other containing the old prices with the same codes (sheet2). Now after several trials to copy the new prices from sheet 1 to sheet 2 with check if the code is same . ( I couldn't )
So how to get rid of the 20 spaces from Sheet1 column A.
I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working
Function HypCon(CorpAcct, Subdiv)
Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5..............
Removing Trailing Commas
I have a list (general ledger) GL codes with trailing commas
Example:
123456789,123456722,123789456,,,,,
When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.
The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes
Is there a function that removes trailing commas, or commas that are not followed by numbers?
I'm currently using the following code to concatenate several cells into one cell, but it also displays the cells that contain blanks.
View 5 Replies View RelatedThe below code removes trailing spaces, but for some reason it does not remove space from this, i clicked F2 and the space is still there, why does it work on some but not others?
98956P102
Sub test()
With ActiveSheet.UsedRange
.Value = Evaluate("if(" & .address & """"",trim(" & .address & "),"""")")
End With
End Sub
I am doing a vlookup on fields such as 02-0223. In one table, there is a space at the end of 02-0223 and in the other table, there is no space. How can I delete the space so the numbers match in vlookup?
View 7 Replies View RelatedAny easy way to remove all spaces from a cell, both leading and trailing? I find it hard to believe that Excel doesn't have this functionality. I don't particulary want to write a VBA script since I have never done it but if that's the only way, I'd love to know how to write it. I have looked everywhere but obviously not in the right places.
View 4 Replies View Relatedhow to remove multiple leading and trailing spaces while leave spaces in the middle of the string in place? I have tried text to columns but this does not remove the multiple spaces.
View 9 Replies View RelatedI wrote a tool that people at work use. They initially need to paste in a bunch of customer locations with Address, City, State, Zip, etc. Sometimes the Users have "bad input" data that has non-breaking spaces, multiple space between words, or leading and trailing spaces and nonbreaking spaces. I have code to get rid of all of those problems. However, the Users often use their data for other important functions at work. So I want to give them a message to let them know that their Original Data is "bad".
So instead of just "Fix" . . . I want to "Report the problem", then "Fix". I need to identify exactly what problem was found - not just tell the User that their data is bad.
I wrote a simple Search routine with error handling that identifies 2 of the 4 cases and notifies the User:
Case 1) ASCII 160 (non-breaking space, HTML  
Case 2) multiple spaces (2 or more consecutive spaces)
Case 3) Leading or Trailing Spaces (ASCII 032)
Case 4 Leading or Trailing non-breaking spaces (ASCII 160, which is HTML  )
I cannot quite figure out how to find the 3rd and 4th Cases. If anyone can help me with Case 4 especially, then I can probably do the same thing for Case 3.
I think it will work to somehow use this idea - the code is not even real code but it is just conceptual:
RIGHT(CellReference, 1) = Char(160) or Char(032)
LEFT(CellReference, 1) = Char(160) or Char(032)
Anyway, here is what I have so far . . .
Sub NotifyBadInput
ErrorFlag = False
Cells.Select ' select entire worksheet
' BAD INPUT 1 - lLook for any occurence of ASCII 160 (non-breaking space, HTML  )
' and Notify the User if any of his Input cells contain  's
On Error Goto errormsg1
Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
While entering data space is given in the first and last of each cell content. For example
if there is a word Alex Patrix in a cell, space is given before A of alex and after x of Patrix. This is done fo many cells. I want to remove only initial and last space which is un-necessary.
The space caused problem to compare cell so i've to remove space.
I've around 2500 cells with this problem.
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 use currency data pasted from legacy program that pastes dollar values followed by a C or D to represent debit and credits, ie -- 1,000,000.00C or 1,000,000.00D.
Is there some way to remove the letter and correctly format the value to a negative or positive number on a whole worksheet or column?
Currently I am doing this manually one cell at a time.
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateInterest rateweekday 24-Jan-889.871 35-Jan-889.992 46-Jan-8810.053 57-Jan-8810.094 68-Jan-8810.135 711-Jan-8810.051 812-Jan-8810.072 913-Jan-8810.283 1014-Jan-8810.114 1115-Jan-889.985 1218-Jan-889.871 1319-Jan-889.962 1420-Jan-889.913 1521-Jan-889.864 1622-Jan-889.715 1725-Jan-889.71 1826-Jan-889.762 1927-Jan-889.693 2028-Jan-889.564 2129-Jan-889.555 221-Feb-889.741 232-Feb-889.622 243-Feb-889.643 254-Feb-889.684 265-Feb-889.755 278-Feb-889.931 289-Feb-889.852 UK interest daily [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.