LinkBack URL
About LinkBacks
Hi,
This must be a common issue. I'd like to replace/remove the LineFeed character (a type of square) in an Excel file. How can I do that?
Cheers
Hi,
This must be a common issue. I'd like to replace/remove the LineFeed character (a type of square) in an Excel file. How can I do that?
Cheers
Go to Edit|Replace
In the Find What: hold the Alt key down and type 010 with the number pad of the keyboard
leave the Replace With field empty.
Click Replace All.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thank you!
I can now find/replace the lineFeed character. That's fine...
However, my cells squeezed because there is no lineFeed character left, but the squares are still here! I suppose when I opened my DBF file initially, the squares indicates where the lineFeed occurs but are not actually the lineFeed characters.
Then, there must be another combination of character such as ALT+number to Find/Replace the squares?
maybe it's 013?
One way to figure out the code is to use a formula such as the following
=Code(Mid(A1,3,1)) where A1 contains the square somewhere in the cell and 3 is the position within the text string that the character appears...
use the code that is returned... if it is a 2-digit code, then precede with a 0.
Hummm! The code doesn't work... The CODE function is ok on its own and it returns the numeric code of the first character of the string. But when you nest the MID function, it doesn't work!
I don't see how it won't work....
Does the Mid() function on it's own return the character?
No, this is exactly what I tried. Just using the MID function
With a cell "Blanca", =MID(A1, 1,1) should return B
There is nothing wrong in that? But doesn't work!
So how doesn't it work... what do you get back?
Is there perhaps a space before "Blanca"... ie. " Blanca"?
OK, found it!
I need to use semi-colon and not comma (as shown in the Excel Help section!).
=CODE(MID(A1;7;1))
Then, I get character 13.
This means I have character 13, then character 10.
But if I try to replace just the character ALT+013, the Carriage Return disappear and there is everything on a single line. I need to keep the layout but just remove these squares.
Oh...so you are using the European version of Excel...should've thought of that...
Did you try Format|Cell and go to the Alignment tab and select Wrap text?
This will not show the CR character, but will put the text in separate lines within the cell.
OK, I managed to solve my problem.
I used =SUBSTITUTE(B2;CHAR(13);"") to remove character 013 and I then used the Warp Text option.
Stange however!
Thank you for your help.
So what does wrapping text accomplish after you substitute the CR character?
the Substitute removed the character 013 but character 010 was still here and displaying as a square, and all on a single line. I then use Wrap Text to "use" the square character to fit the text on multiline.
I understood the logic behind Excel, but not all!
Regarding the semi-colon and common in the functions, I think it depends on the language settings on the machine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks