Ask Your Question

Accidentally deleted spaces in all B columns. [closed]

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

Chloe gravatar image

updated 2020-08-16 12:33:38 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-13 10:35:16.334667

1 Answer

Sort by » oldest newest most voted

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

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

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 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-25 10:15:24 +0100 )edit

Question Tools

1 follower


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

Seen: 47 times

Last updated: Feb 25 '17