Ask Your Question

Accidentally deleted spaces in all B columns.

asked 2017-02-25 08:44:14 +0200

Chloe gravatar image

I highlighted column B and then used CTRL+H to replace " " with "" to delete all spaces in the B column of sheet 55 of my spreadsheet. I accidentally did not tick current selection only and it removed the spaces from all instances of column B in sheets 1-54. I have continued working so its now impossible to go back and undo without losing all the work (I use it for stock taking).

I now (ideally) need to replace the spaces in the damaged B columns. Most (though probably not all) now appear in the format..."Makaton-WhenTheDeadRiseFromTheSea". So there are upper case letters or hyphens after where the spaces used to be. I know how to fix the hyphens (replace "-" with " - "). Is there anyway to correct using the fact that the program can read upper case letters?

Thanks! Chloe

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-02-25 09:54:01 +0200

updated 2017-02-25 09:56:39 +0200

Use case-sensitive search (Match case), and regular expressions. Replace "([A-Z])" with " $1", then replace "^ " with empty string "" (to remove leading spaces).

edit flag offensive delete link more


Thank you so much. Super helpful.

Is there a way to prevent it applying to complete upper case words. Like DJ or IOU or whatever. I don't really need those separating if possible.

Chloe gravatar imageChloe ( 2017-02-25 10:05:54 +0200 )edit

Then use "([A-Z][^A-Z])" search string instead

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-25 10:15:24 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-25 08:44:14 +0200

Seen: 41 times

Last updated: Feb 25 '17