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..
Edit > Replace, in the Find what box, press and hold the Alt key, and on the NUMERIC keypad, type 0010 , then Replace All.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
unfortunately nothing is happening. when i press and hold alt.. and type in the numeric code.. nothing is happening. the box is just remaining empty. any idea why this might be?
The new line character does not have a displayed character, like a normal letter.
What you should notice though is the cursor will drop in the Find Text box as it has moved to the next line.
I am also unable to use the Alt+numeric keypad route to generate new lines (and tabs and carriage returns). I have yet to discover why (and I have been looking for a while and have asked the great and the good in the online forums!).
An alternative method to get one is just to use the following formula in an empty cell:
=CHAR(10)
and copy and pastespecial values on this cell. Then with the cell selected use F2 to edit and select the new line character (which may well appear as simply a new line in the cell) and Ctrl+C to copy it - then you should be able to Ctrl+V in the Edit>Replace dialog.
Richard
Richard Schollar
Microsoft MVP - Excel
I don't think you can get NewLine or CarriageReturn by using
ALT+010 or ALT+013 on the numeric keypad when editing a cell.
The ALT+010 works in the FIND/REPLACE dialog
When i try that.. the find/replace function isn't reading the character.
Maybe if i give an example of what i am trying to do it, there might be another way to do it..
For Example
My Name
is
John. I Live in a
house
This is way i have the data..
I want to be able to present it like
My names is john. I liive in a
house.
and let it go on to a new line when it reaches the end of the cell
when i try that. the alt+010.
its returns a message saying its excel can't find a match.
is it possible that excel is reading it as something different since it was copied in..
can you post an example workbook so we can read the actual text values.
the data i am working on is all private.. and i'm i think if i start altering so that its ok to put but, i'll just be changing it to much..
Assuming cell with text in is A1 enter this in B1 and copy down for enough rows to cover the length of the text.
=CODE(MID($A$1,ROW(),1))
was messing bout there.. and if i wish the TRIM function in the text fucntion. it will print it all on line. the problem still remains though. if i double click in the cell and then outside it again it resorts back to the original. however. before this. the lines appear as a (excuse the lack of techinal symbol) a box with a question mark in it.
if one types =char(10) this is symbol. all i want to do at this stage it replace this symbol with a space but sitll having the same problem
try to find what code it is by puting cursor at end of last word of first row and delete just the letters using back space , then use =code(a1) on mine it returns 10 but you may have a different one in there from where it was copied
so
martin
wilson
test
becomes
wilson
test
andy.. i am presuming that peice of code you show me.. represents the ascii codes for the individual characters.. showing the character up as 10. been the space.. wat i want to do it change this '10' to a '32' .. 32 been the spacebar character..
I know and the suggested find/replace would normally work.
As you can not post an example of the cell so we can confirm I gave you a formula to display the numeric value of all of the characters.
Is 10 in the correct place of the new line?
You could try this in the VBE immediate window.
Code:activecell.Value = replace(activecell.Value,chr(10)," ")
Posting Permissions
Bookmarks