Forum Discussion
Deleting CRs in a cell
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.: A...
Hi Nicholas
If the cells are in row order (e.g. B2, B3, B4, etc), you could maybe try the following and see how it goes:
1. In another blank column, put in the formula for Col A and Col B:
Col A
=LEFT(A2,4)
Col B
=LEFT(B2,4)
This will extract the first 4 characters (your % amount) and ignore the rest.
2. Drag the formula down to cover the cells.
3. Copy the cell output.
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.
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.
Hope that helps!
Cheers
Damien
Thanks Damien it works brilliantly. Best wishes Nick
- Hi Nick
I'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!
Cheers
Damien
