SOLVED

How to edit cells

Copper Contributor

Hello!

 

I'd like to know how to make one-cell spaces beween the text-cells generally. I know how to do it one-by-one but it takes a lot of time. I'd like it boost.

 

Can you help me?

 

Your sincerely:

 

Josey

9 Replies

@Zsmbek1963 

It would probably be possible with a macro, but we'd need to know more about the layout of your data.

 

Wouldn't it be easier to increase the column widths and/or the row heights?

@Zsmbek1963 

Many ways lead to a solution in Excel :)

 

with "Format Cells..."

a)  Select all cells where you want to add a space between the numbers. Of course, you can also mark entire rows or columns (hold down the Shift key and select all columns/rows).

b)  Right-click on the marked cells to select "Format Cells..." from the context menu. This opens a window in which you can make various settings.

c)   In the "Numbers" menu, you must now click on the "User-defined" category and type in a so-called "Type". For example, for spaces between three numbers "xxx xxx xxx xxx"

or between two numbers "xx xx xx xx".

You can use this pattern to create any bundle of numbers and thus the spaces.

 

If you want to use a formula:

CONCATENATE function

Use CONCATENATE, one of the text functions, to join two or more text strings into one string.

Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.

 

I would be happy to know if I could help.

 

NikolinoDE

I know I don't know anything (Socrates)

Hi!
Unfortunately, increasing column widths or row is not an option. I am making a subtitle-translation for a theatre performance and for the subtitle-arranging program I have to leave one-cell spaces between each text-cell.
Being an absolute beginner in Excel, would you so kind to tell me, what is macro?

Thank you in advance:

Zsmbek1963
Dear NikolinoDE!

I tried them, but did not work. I'm a really a rookie in
Excel. Would you give me a more specific key-instruction?

Thank you:
Zsmbek1963

@Zsmbek1963 

Do you want to insert an empty column in between the filled columns?

Do you want to insert an empty row in between the filled rows?

Or both?

@Zsmbek1963 

Here are some examples in the attached file, maybe this will help you :).

 

The latter. I would like to insert empty row in between filled rows.
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@Zsmbek1963 

Save the workbook before you do the following.

If you don't like the result, you can close the workbook without saving it.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module (or press Alt+I, M) to create a new code module.

Copy the code listed below into the code window.

With the insertion point anywhere in the code, press F5 to run it.

Switch back to Excel to inspect the result.

If you won't need the macro anymore, save and close the workbook. Excel will display a warning that macros will be lost. Click Yes to save the workbook anyway.

If you'd like to keep the macro, save the workbook as a macro-enabled workbook (*.xlsm).

You will have to allow macros when you open it.

 

The code:

Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub
Thank you very much!
1 best response

Accepted Solutions
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@Zsmbek1963 

Save the workbook before you do the following.

If you don't like the result, you can close the workbook without saving it.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module (or press Alt+I, M) to create a new code module.

Copy the code listed below into the code window.

With the insertion point anywhere in the code, press F5 to run it.

Switch back to Excel to inspect the result.

If you won't need the macro anymore, save and close the workbook. Excel will display a warning that macros will be lost. Click Yes to save the workbook anyway.

If you'd like to keep the macro, save the workbook as a macro-enabled workbook (*.xlsm).

You will have to allow macros when you open it.

 

The code:

Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub

View solution in original post