Tab key equivalent in Excel.

J

JRC

Dear all:

I need to find a way to move the contents of a cell to the right with
a simple key stroke similar to the way we move text to right with the
tab key. This is in fact for working with text files imported into
Excel where some of the text needs to be "indented" more (and
sometimes less, although rarely).

What key can I use in Excel that will shift the contents of a cell to
the next one to the right ? Copying or cutting and pasting wouldn't
work as I am working with an extremely large document and it would be
far too time consuming.

Any help will be very appreciated.

Thanks,


JRC
 
J

JE McGimpsey

JRC said:
Dear all:

I need to find a way to move the contents of a cell to the right with
a simple key stroke similar to the way we move text to right with the
tab key. This is in fact for working with text files imported into
Excel where some of the text needs to be "indented" more (and
sometimes less, although rarely).

What key can I use in Excel that will shift the contents of a cell to
the next one to the right ? Copying or cutting and pasting wouldn't
work as I am working with an extremely large document and it would be
far too time consuming.

Any help will be very appreciated.
Click to expand...

One way (three keystrokes):

Ctrl-i, CMD-r, RETURN


Which will bring up the Insert dialog, select Shift to Right, and
dismiss the dialog.

If you're using XL08, you could use AppleScript:

tell application "Microsoft Excel"
insert into range selection shift shift to right
end tell

Which you could save to the Excel AppleScript menu and give a keyboard
shortcut (see

http://www.entourage.mvps.org/script/add_shortcuts.html

for details).

If you're using XL04, you could use VBA:

Public Sub ShiftSelectionToRight()
Selection.Insert Shift:=xlToRight
End Sub

Save this in your Personal Macro Workbook and assign it a keyboard
shortcut Tools/Customeize/Customize Keyboard...

ALTERNATIVELY:

You might also, since you're working with text, use XL's Indent feature,
which by default has the keyboard shortcut CTRL-m (or CTRL-OPT-TAB) to
increase the tab
 
J

JRC

If you have control over the creation of the Text file you may be able to
approach from the opposite direction. Perhaps there are output options in
the creator app that would render a resulting file that wouldn't have to be
adjusted after the importing?

Also, when you import the data pay particular attention to the 2nd page of
the Import Wizard -- particularly the checkbox pertaining to the treatment
of consecutive delimiters. That can account for data shifting in records
which are null in any given field.

BTW: Whenever posting please be sure to specify your version & update level
of the software. You have been given 2 different macros from 2 different
responders. Both macros are designed to do the same thing but note the
differences... I'm no VBA guy but I'm guessing that the 2 may not be
interchangeable from Mac to Windows & neither is of any value if using
Office 2008. There's also an AppleScript kindly offered by John which is
worthless if you're using a Windows version of the program.

HTH |:>)
Bob Jones
[MVP] Office:Mac
Click to expand...


Hello macropod, John and Bob.

First I wish to thank you for your prompt replies as well as for the
helpful suggestions on how to address my project.

Good point, Bob. I should have mentioned the system and verison I am
running. My system is a Mac G5 running the latest version of Leopard
(not Snow Leopard) which is 10.5.8 and I have Microsoft Office 2004
(haven't upgraded to 2008 and will probably wait until next version to
upgrade). The suggestion to use VBA brings a lot of questions to mind
and I am happy I can still benefit from it since I am running 2004.

Taking my question a step further, the file I am working with is
rather long (close to 57,000 entries or lines of text) that need to be
slightly edited. I would certainly prefer to do it using a macro or
some other automated solution otherwise I will have to edit the entire
file manually one line at a time. Below is an example of the type of
file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the numeric code. Lines
with an alphanumeric code containing only 3 characters stay on the
left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 14 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Thank you in advance for your help.


JRC
 
M

macropod

Hi JRC,

Try:
Sub Indenter()
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 1 To UBound(Split(.Cells(i, 1).Value, "."))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


If you have control over the creation of the Text file you may be able to
approach from the opposite direction. Perhaps there are output options in
the creator app that would render a resulting file that wouldn't have to be
adjusted after the importing?

Also, when you import the data pay particular attention to the 2nd page of
the Import Wizard -- particularly the checkbox pertaining to the treatment
of consecutive delimiters. That can account for data shifting in records
which are null in any given field.

BTW: Whenever posting please be sure to specify your version & update level
of the software. You have been given 2 different macros from 2 different
responders. Both macros are designed to do the same thing but note the
differences... I'm no VBA guy but I'm guessing that the 2 may not be
interchangeable from Mac to Windows & neither is of any value if using
Office 2008. There's also an AppleScript kindly offered by John which is
worthless if you're using a Windows version of the program.

HTH |:>)
Bob Jones
[MVP] Office:Mac
Click to expand...


Hello macropod, John and Bob.

First I wish to thank you for your prompt replies as well as for the
helpful suggestions on how to address my project.

Good point, Bob. I should have mentioned the system and verison I am
running. My system is a Mac G5 running the latest version of Leopard
(not Snow Leopard) which is 10.5.8 and I have Microsoft Office 2004
(haven't upgraded to 2008 and will probably wait until next version to
upgrade). The suggestion to use VBA brings a lot of questions to mind
and I am happy I can still benefit from it since I am running 2004.

Taking my question a step further, the file I am working with is
rather long (close to 57,000 entries or lines of text) that need to be
slightly edited. I would certainly prefer to do it using a macro or
some other automated solution otherwise I will have to edit the entire
file manually one line at a time. Below is an example of the type of
file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the numeric code. Lines
with an alphanumeric code containing only 3 characters stay on the
left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 14 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Thank you in advance for your help.


JRC
 
C

CyberTaz

Thanks for the detailed response -- I'll leave the VBA stuff to those who
know what they're doing :) but, FWIW, I truly believe you'd be better off
to do this in Word rather than Excel.

Good Luck |:>)
Bob Jones
[MVP] Office:Mac



If you have control over the creation of the Text file you may be able to
approach from the opposite direction. Perhaps there are output options in
the creator app that would render a resulting file that wouldn't have to be
adjusted after the importing?

Also, when you import the data pay particular attention to the 2nd page of
the Import Wizard -- particularly the checkbox pertaining to the treatment
of consecutive delimiters. That can account for data shifting in records
which are null in any given field.

BTW: Whenever posting please be sure to specify your version & update level
of the software. You have been given 2 different macros from 2 different
responders. Both macros are designed to do the same thing but note the
differences... I'm no VBA guy but I'm guessing that the 2 may not be
interchangeable from Mac to Windows & neither is of any value if using
Office 2008. There's also an AppleScript kindly offered by John which is
worthless if you're using a Windows version of the program.

HTH |:>)
Bob Jones
[MVP] Office:Mac
Click to expand...


Hello macropod, John and Bob.

First I wish to thank you for your prompt replies as well as for the
helpful suggestions on how to address my project.

Good point, Bob. I should have mentioned the system and verison I am
running. My system is a Mac G5 running the latest version of Leopard
(not Snow Leopard) which is 10.5.8 and I have Microsoft Office 2004
(haven't upgraded to 2008 and will probably wait until next version to
upgrade). The suggestion to use VBA brings a lot of questions to mind
and I am happy I can still benefit from it since I am running 2004.

Taking my question a step further, the file I am working with is
rather long (close to 57,000 entries or lines of text) that need to be
slightly edited. I would certainly prefer to do it using a macro or
some other automated solution otherwise I will have to edit the entire
file manually one line at a time. Below is an example of the type of
file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the numeric code. Lines
with an alphanumeric code containing only 3 characters stay on the
left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 14 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Thank you in advance for your help.


JRC
Click to expand...
 
J

JE McGimpsey

JRC said:
Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric code.
Click to expand...

One way (with tabs)

Public Sub TabIndentText()
Dim sTabs As String
Dim sTemp As String
Dim rCell As Range
Dim nCount As Long
Dim nSemi As Long

sTabs = String(10, vbTab)
For Each rCell In Selection
With rCell
nCount = Len(.Text) - _
Len(Application.Substitute(.Text, ".", ""))
nSemi = InStr(1, .Text, ";")
.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & _
" [" & Trim(Mid(.Text, nSemi + 1)) & "]"
End With
Next rCell
End Sub

Another (with Indents):

Public Sub IndentText()
Dim rCell As Range
Dim nCount As Long
Dim nSemi As Long

For Each rCell In Selection
With rCell
.IndentLevel = Len(.Text) - _
Len(Application.Substitute(.Text, ".", ""))
nSemi = InStr(1, .Text, ";")
.Value = Trim(Left(.Text, nSemi - 1)) & " [" & _
Trim(Mid(.Text, nSemi + 1)) & "]"
End With
Next rCell
End Sub
 
J

JE McGimpsey

macropod said:
Try:
Sub Indenter()
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 1 To UBound(Split(.Cells(i, 1).Value, "."))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
Click to expand...

Macropod -

Office04 is VBA5 and does not contain the Split() command.
 
M

macropod

Hi Jim,

That's a drag - even Excel 2000 supported Split. Assuming 'Replace' is supported:
Sub Indenter()
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 1 To Len(.Cells(i, 1).Value) - Len(Replace(.Cells(i, 1).Value, ".", ""))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
End Sub

If 'Replace' isn't supported, one should at least be able to use 'WorksheetFunction.Replace'.
 
J

JRC

 JRC said:
Basically I need the format to go from the existing:
Click to expand...
Abdominal Cavity;A01.047.025
Click to expand...
Where the term is followed by a semicolon and alphanumeric code.
Click to expand...
To the following:
Click to expand...
Abdominal Cavity [A01.047.025]
Click to expand...
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric code.
Click to expand...

One way (with tabs)

 Public Sub TabIndentText()
    Dim sTabs As String
    Dim sTemp As String
    Dim rCell As Range
    Dim nCount As Long
    Dim nSemi As Long

    sTabs = String(10, vbTab)
    For Each rCell In Selection
       With rCell
         nCount = Len(.Text) - _
                        Len(Application.Substitute(.Text, ".", ""))
         nSemi = InStr(1, .Text, ";")
         .Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & _
                " [" & Trim(Mid(.Text, nSemi + 1)) & "]"
       End With
    Next rCell
 End Sub

Another (with Indents):

 Public Sub IndentText()
    Dim rCell As Range
    Dim nCount As Long
    Dim nSemi As Long

    For Each rCell In Selection
        With rCell
            .IndentLevel = Len(.Text) - _
                    Len(Application.Substitute(.Text,".", ""))
            nSemi = InStr(1, .Text, ";")
            .Value = Trim(Left(.Text, nSemi - 1)) & " [" & _
                    Trim(Mid(.Text, nSemi + 1)) & "]"
        End With
    Next rCell
 End Sub
Click to expand...

Hello, Jim.

Thank you for your prompt reply and help.

I have very little experience with macros and VBA but I tried the code
just you suggested. Following instructions from a book I have on MS
Office I entered the code and when I tried to run it the first line
was highlighted in yellow and it gave me a message stating that there
was a syntax error.

What am I doing wrong and how can I fix it ?

Thank you again for your help.

JRC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Modern equivalent of Autotext in Word? 0
Cell data entering slow! 0
Paragraph numbering in Word 1
Excel does not accept text format! 0
Linking data in MS Word into Excel 5
Autonumber field in Excel that behaves the same as Access 0
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3

Top