Deleting CRs in a cell

Question

In using NCER Nexus to generate 3 year trend data output is generated in Excel format. The problem I have is that some cells contain two pieces of data separated by a carriage return e.g.:&nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B1 &nbsp; 2016 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 20172 &nbsp; 73% &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 63%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; -10ptsB2 contains both 63% and the calculated difference from the 2016. There are some 3 000 data sets. Is there a way of deleting the carriage return and -10pts; leaving just the value 63%?

nicholas horn · Answer

Thanks Damien it works brilliantly. Best wishes Nick&nbsp;&nbsp;

damien_rosario · Answer

Hi Nicholas&nbsp;If the cells are in row order (e.g. B2, B3, B4, etc), you could maybe try the following and see how it goes:&nbsp;1. In another blank column, put in the formula for Col A and Col B:&nbsp;Col A=LEFT(A2,4)&nbsp;Col B=LEFT(B2,4)&nbsp;This will extract the first&nbsp;4 characters (your % amount) and ignore the rest.&nbsp;2. Drag the formula down to cover the cells.&nbsp;3. Copy the cell output.&nbsp;4. Select the cell range from Col A, right click and choose Paste (Values). This will replace the data in the cells with the filtered ones using the formula above.&nbsp;Hopefully that works (a sample spreadsheet is attached). You may need to play with it a bit to get it to work the way you want.&nbsp;Hope that helps!&nbsp;&nbsp;CheersDamien

damien_rosario · Answer

Hi NickI'm glad to hear it! I would suggest marking the solution as best response to close the loop on this case for other forum users to note.Best wishes for your Excel work!CheersDamien

Forum Discussion

Deleting CRs in a cell

Share

Resources