Forum Discussion

Zsmbek1963's avatar
Zsmbek1963
Copper Contributor
Feb 27, 2023
Solved

How to edit cells

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

  • 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
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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)

  • 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's avatar
      Zsmbek1963
      Copper Contributor
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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?

Resources