Replacing Multiple Spaces with Line Breaks

ryanh6

New Member
Joined
Jul 7, 2016
Messages
21
I need a quick fix for an issue I've been having. Basically I have a data base, and I send versions to people to populate. However, in the excel sheet that I have sent, people have been inserting multiple spaces, in place of a line break. I need to remove these spaces and replace them with a line break, without removing the spaces that belong between words. I have tried many combinations of the substitute and trim function but have not found any solution. Thanks in advance!! See below for example:

123-456-789.0
987-654-321.0 456-789-123.0
123-456-789.0 Document Number1 987-654-321.0 Document2 456-789-123.0 DocumentName 3

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Or replace the spaces with another character as the editor of the page deletes surplus pages so they are not visible.
 

ryanh6

New Member
Joined
Jul 7, 2016
Messages
21
Or replace the spaces with another character as the editor of the page deletes surplus pages so they are not visible.

Each cell would be in this format, with 700 cells in this format, except spaces instead of "-"

Thanks!
123-456-789.0 Document Name 1-----------------------------------------987-654-321.0 Document Name2-----------------------------------------456-789-123.0 DocumentName 3

<tbody>
</tbody>
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Find and replace:

In the Find what, type space space

In Replace, Ctrl+J

Replace all until there are no replacements

Then replace Ctrl+J Ctrl+J with Ctrl+J until there are no replacements

Takes longer to describe than do.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,967
Messages
5,599,093
Members
414,285
Latest member
excela2z

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top