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


Deleting New Line Character

A have a worksheet in excel which was copied from pdf.

in each cell number of different lines, the data appears on a new lines. the same as using the new line character. (ALT + ENTER)

The document is huge. well over 3000 individual cells, and generally 4 or 5 lines in each one. Is there anyway i can globally change the whole documents. Just deleteling all the new lines??

Using excel 2007 on windows xp..

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Remove Vertical Line Character
How do I search for and remove the vertical lines in the above example from the column D? These things came when I imported a data file into Excel....

View Replies!   View Related
Concatenate Cells & Replace Certain Character With Line Breaks
I have 2 sheets in a work book. Sheet 1 I paste information from an email into A4.
Sheet 2 gathers the information and places it in the cell formating the text so I can import it properly to another program.

I want to take notes that a person fills in (they fill out an online form with their personal information and sometimes leave comments, but not always) and paste this into Sheet 1. When I paste the comments into Sheet 1 it is pasted into mulitple cells. I want sheet 2 to format the text from these mulitples cells into one cell with spaces placed correctly ....

View Replies!   View Related
Replace Small Square Character With Line Break After Import
When importing text from a CSV, the 'line returns' appear in a cell as a small square with faded left and right sides. The do force a line break as intended. However, they are a nuisance in reading and printing.

When I use ALT+ENTER to force a line break, there is no visible character (on screen or printed) for the line break, which is as it should be.

How can I replace this 'special character' line break with one not visible?

View Replies!   View Related
Add Character After 1st Character Of Cells
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.

Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}

I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?

My formulaic solution is thus: .....

View Replies!   View Related
Append All Digits Before Character To All Numbers Between The Same Character
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?

Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.

View Replies!   View Related
Look For Character In Cell And Insert New Character - For More Than One
I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help

Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View Replies!   View Related
Put All Values Of A Specifice Line In That Matrix In A Line
I'm working with matrix in vba and i'm trying to put all values of a specific line in that matrix in a line in excel. For example:

Matrix =
1 2
3 4

and i want Range("B1:B2") = 3 | 4 (the second line of that matrix).
Is there any way of doing this without perfoming a loop?
[something like Range("B1:B2").value = 'range'(matrix(0,0) , matrix(0,1)) ]

View Replies!   View Related
Convert Macro In 1 Line To Multiple Line Code
i hv following code

(i use generate macro)

my question is how to arrange the code from one line to multiple like :-

following code show in excel macro environment is one striaght line.

' Create new var on yr , and replace 2006 to CY06.

ActiveCell.FormulaR1C1 = _

View Replies!   View Related
Line Graph Not To Continue The Line If There Is Nothing In Row 10 Of The Table
With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.

for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.

View Replies!   View Related
Report A List Line By Line Based On Name Criteria
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View Replies!   View Related
Delete A Line.or Rather A Line On A Line
I have a spreadsheet where there is a white line that is over a row line. No matter what I do I cannot seem to delete it.

I zoom in but I cannot select it.

I change the border of the cells and it will not disappear.

I am completely lost as to what else it could be, no doubt it is a simple answer that is staring me in the face. I inherited the spreadsheet so not sure what was done in the past.

View Replies!   View Related
Line Graph: (line Graph To Display An Amount Over Time)
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.

A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:

Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):

Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).

View Replies!   View Related
Find Words - Deleting The Words - And Deleting Some Info After The Words.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.

I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.

View Replies!   View Related
If Then Containing Character
I would like to create an If Then statement in which the condition is based on if a string is a simple text or a formula. Some of my cells contain a formula and some contain simple text...and I would like to cycle through all of them- only do something different to them based on what the cell contains.

View Replies!   View Related
Recognises What Character Is That
do you know any function that recognises which character is in a particular cell?

View Replies!   View Related
Is There A Substitute Character That Can Be Used
I have a whole bunch of dates in a column that look like this:

5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.

*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.

View Replies!   View Related
Replace Character With Another
I'm trying to go through a short list of letters and change the contents of every cell in the list that contains a "c" into a "M". I've written this code, but I get a #Value! message on my spreadsheet when I run it:

Function FindCpmDpm(SearchRng As Range)

Dim CelRng As Range

For Each CelRng In SearchRng

If CelRng.Value = "c" Then
CelRng.Value = "M"
End If

Next CelRng

End Function

I am having trouble accessing the cell which contains the "c".

View Replies!   View Related
Position First Character
I have some cells with some spaces and then text where I want to find the position of the first letter. Do any of you know about any function I can use?

The example below contain 5 spaces and then my name. It should give me the answer 6.

" Magne"

View Replies!   View Related
Character Limits
I copied this from Excel:

Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

I have an Address Book from an old Personal Information Manager that exports the file as ASCII.

My problem is when I view the .asc file in Excel, Excel has created two rows for several entries.

That creates a problem as I wanted to select Column "A" then do Text to columns with a comma delimiter (the asc files has the comma in place)
However, since Excel put in the second line, I'll not be able to put in a header column.

When I look at the .asc file there is no way that there is more than 32,767 characters in each row. So why did Excel split it up? ....

View Replies!   View Related
Character Limit?
I'm adding some necessary code to the beginning and end of some cell contents in Format | Cells | Custom and I seem to be coming up against a character limit.

I'm using the phrase "!!<"@">!!" to add the code, but with some cell contents the result is ########### (but much longer), and I'm having to remove some of the text to make it work. The limit seems to be around 255 characters.

View Replies!   View Related
Word After Last Specified Character
I am new to Excel and just starting with formulas. I am wondering if a formula can handle the following:

We have a column that has a url in each cell. Example (I removed the http:// before it so that all the words show up and it doesn't change to a clickable link):[/url]

We would like to tell it to copy the portion after the last forward slash and until the period and put it in another cell. In the above example it would give us sylvantropicalbirdi. We then would want to append a _t to it.

Is this possible or am I just wasting my time trying to figure it out?

View Replies!   View Related
Cells Containing Specified Character
What formula should I use to search for cells which have a certain single letter or digit.

Ex. Those with the letter O

2007-FHYO-45 YES
2006-FHY-45 NO
2007-FYY-38 NO
2007-FYY-21 NO
2007-FGHO-81 YES

View Replies!   View Related
Test To See If A Character Is A Number
I have a number of entries in column A.
The format is:

A1 B680192 Middle School
A2 Office Building Project

and so on;

I want to test if char.s 2 through 7 are numbers.

I can seperate one char at a time and use CODE function to check if the result is between 48 and 57.

I know how to split all six char.s at a time e.g. 680192, but I don't know how to test to see if it is a number?

ISNUMBER function doesn't work.

If I can do it with excel function, it would be better. If VBA has to be used, so be it.

View Replies!   View Related
Wildcard Character In IF Condition
I have a little problem in using wildcard character in IF condition.

For I = 2 to LastRow
If Activesheet.cells(I,5).Text = "ENF*" then
End If
Next I

I want the code to search for any word starting with ENF and then take the necessary action.

Could anyone tell me how to make use of wildcards in a situation like this.

View Replies!   View Related
Change Character Macro
In column A if have strings like this: X-100-X10 or C-100-1X00 etc.

I need to change the X at the end of the string to "." e.g. X-100-.10 or C-100-1.00

Can't figure it out since there can be 1 "X" or more than 1 "X" and the second "X" that needs to get changed can be in a different location i.e. not alway 3 from the right or 7 from the left.

Is there some macro code I can use to make this change? Otherwise I have to change hundreds of these manually.

View Replies!   View Related
Special Character Remove
I have sentences which contains special characters (mentioned below) in a single column. I need to remove all special characters other than space. Could anyone help me on this...


View Replies!   View Related
Extract First Character Of String
I have a column that has various codes such as "E123" and "X456" and "S345". I just want to extract the numbers and leave the letters in each cell. How do you write a formula using the LEFT function to extract the last three numbers?

View Replies!   View Related
Add Alpha Character
I want to add a letter to the end of value and have it start over at "A" when the cell value changes. For example

My Column contains

I want this in another column

View Replies!   View Related
Symbol Appear When I Type A Certain Character
I am trying to create a document and i'm not sure if this is possible or not, but i'd like to type in a number in column A (1-10) and then in column B have a symbol appear corresponding to the number.

So if i type in 5 in Column A, Column B will have 5 squares pop up (or something along those lines), etc.

View Replies!   View Related
ASCII Code For Character
Find the ASCII character code for the characters in cells A3-D3? I have a problem importing data into the database because of these cells. They have some weird properties: ISBLANK would return them FALSE; however if I leave blank space in "Find and Replace" it will find and replaces those cells together with cells which are genuinely blank. So I could theoretically go and replace all blank cells to some character (for example ^), then do the opposite and replace ^ into blank and it would solve the problem, but I am curious as to what exactly this character is.

View Replies!   View Related
Split Text After Specified Character
You know how you can combine different cells and concatenate their values? Can you reverse that? For Example, I have this value "Yambao, Mikhail P." and I want to break them apart so I can use "Yambao" as a reference value to fill up other information concerning that name.

View Replies!   View Related
Maximum Character Input
Is their a maximum as to how many characters/words can be fit into one cell on excel 2000? if so what is the limit? and is their anyway to increase this limit?

View Replies!   View Related
VB Script To Remove A Character
I need a VB script to remove a character.

The character is the SECOND . in the following strings

Basically, 1.07.66 represents 1 min and 7.66 sec, but I cant use the data until excel can recognise it as 1.0766

A vb sript would be nice, but a formula is also acceptable.

View Replies!   View Related
Limit First Character To Alpha
I'm trying to have the macro limit the cell entry to 5 characters beginning with a "P" and the remaining 4 character being numeric. The problem I'm having is that it's not recognizing the requirement for the beginning "P" but only identifies if the length is less than 5 characters. Any idea how I can resolve this?

For PN2 = 43 To 72
If Not IsEmpty(Cells(PN2, 1)) And Left(Cells(PN2, 1), 1) "P" And Len (Cells(PN2, 1)) 5 Then
MsgBox ("The employee number must be 5 characters long begining with P (e.g. P0001), please change accordingly!")
Range(Cells(PN2, 1), Cells(PN2 + 2, 1)).ClearContents
Cells(PN2, 1).Select
End If

View Replies!   View Related
Searching A Cell Value For A Character
I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.

View Replies!   View Related
Finding A Character Using If Function
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....

View Replies!   View Related
Splitting Character String
How do I split the below character or similar strings into 2 columns of 25 characters each without cutting a word off?


View Replies!   View Related
Nth Character From String
I would like to get the nth character from a string. For example, let's say there is a string "jupiter", and I would like to get the 3rd character of this string, "p". Is there a function in excel vba that would allow me to do this?

View Replies!   View Related
Remove Forbidden Character
I'm trying to write a UDF that takes the text in a cell and returns text with a list of forbidden characters removed from it. At the moment I have the following:

View Replies!   View Related
How Can I Look For A Character Or An Operator In A FORMULA
Iīm trying to find a way to get a formula which can look if there is a character (letīs say it is "-") in a formula which is, by the way, variable (letīs say each cell has different values like: =899+59-6 and so it goes and goes), and brings me a true or false value.

Specifically it is like this:

I have formulas to be evaluated like the following:


I need a formula which can look into the formula and if it finds the character "-" or a "+", brings me a true or false message.

View Replies!   View Related
Replacement Of Suffix Character
We have two characters : One debit shown as Dr
Another Credit shown as Cr

we have an excel sheet containing a problem as under:
row 1 row2
column 1 Sales 345670 Dr
column 2 Receipt 340000 Cr
column 3 TDS 5670 Cr

We have replaced the Suffix so that it looks as under:

Sales 345670
Receipt 340000 Cr
TDS 5670 Cr

Wewant the last two lines to have figures in the negative ie - 340000
- 5670
i.e. cr suffix change to negative figure -340000

View Replies!   View Related
Reading One Character Of Text
The following code reads text one character at a time, and each character is determined either to be a blank space or to be any other character. If it's determined to be any other character, then the character is added to the active cell. If it's determined to be a blank space, then the active cell becomes the cell one column over. The idea is to have different words written in columns next to each other.

The sample text file I'm reading from reads: "text file". The file has only those two words. One space between them. No space before the first word, and no space after the second word. The following code compiles and runs. BUT the result is that the word "text" is in column k, not A. And the word "file" is in column J, not b.

View Replies!   View Related
How To Remove The Rightmost Character
I have data in a column and each cell has an extra character at the end of it-

school bus>
trip to europe"

I once saw someone remove the characters with either a =LEFT( or =RIGHT( formula with a comma after the reference cell and some kind of FIND" " statement.

View Replies!   View Related
Remove Comma In The First Character
How to remove the first charcter that contains comma, like this :
,001,003,005 and i want the result is --> 001,003,005.

View Replies!   View Related
Formulate Character In A Column?
How to formulate Character


B1 = IF character is 10, I take ABCDE (5 digit) but if only nine I'll take first 4 digit. How could I formulate that?

View Replies!   View Related
Add A Character To Every Cell..
i have a column that is 1900 long and i need to add a "comma" to the end of everything in each cell in that column. How do i go about this? so at the moment it look like this:


But i need to look like this:


View Replies!   View Related
Character Mark In A Box
I've searched for this in the forums but can't find anything. I need to find/replace a character in Excel, it's a question mark in a box and looks like this

I've tried searching for special characters (using the ALT key and number pad) but I can't find anything matching this.

When I save the spreadsheet as a CSV (TAB delimited) this character gets picked up as a TAB, but when I try find replace on the TAB character it doesn't work!

I'm using Excel 2007.

View Replies!   View Related
Adding A Character To A Cell
I have a number in each cell 42189654 and I want to add a period in the 3 place. Example 42.189654. I cannot seem to get the correct formula.

View Replies!   View Related
Removing Character Using Trim..
I need your guys expertise in the following formula. I'm applying the following to a large range of data that varies in lenght,however all of the data has a 1Y or 2Y at the end of it. I need to remove it from the data into a new column. Currently I'm using =trim(mid(A2,1,30) how can I change my formula to obtain my results?

for example: Nationwide Select premium $74.99 1Y
Nationwide Select Premium W/e-mail $74.99 1Y

As you can see the lenght of the data is different.

View Replies!   View Related
One Character Filter Script
Can someone write a script for me so that if the number of characters in cells column 1 is more than 1 then it will delete the entire row? i.e. only retain the rows in which the cells in column 1 have only 1 character.


Column 1


Column 1

View Replies!   View Related
Copyright Đ 2005-08, All rights reserved