= (B4, ( 202 ), ' ) Which removes a series of 4 invisible characters at the start of each cell in column B. How this formula works The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string ('), which effectively removes the character completely. How did I know to remove character 202? To figure that out, I first used this formula to get the code number for the first character of B4.
. Place your cursor in the Find what box. Press and hold the Alt key and then enter '010' from the 10-keypad portion of your keyboard. (Note: The numbers from the top row of the keyboard will not work.) If '010' does not work, try '013', as data imported from another source may have the line breaks represented by a different code. Place your cursor in the Replace with box. Press your space bar once.
If you want to replace these line breaks with a space, you can do this with Excel’s “find and replace” command. Press CTRL-F to launch the “find and replace” window. In the “Find what:” field, enter the line break code, which is character number 10.
This will replace the line break with a space so that the name will appear this way: John Smith. If you leave this blank, the name may look like this: JohnSmith.