Forum Discussion
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
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
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:
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.
I know I don't know anything (Socrates)
- 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
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?
- 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:
Zsmbek1963Do 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?
