The Excel cell contains text with a "-" minus sign embedded in it (e.g. a
phone number).
Q: Is there a Hotkey that will
1) "Find" the minus sign, and
2) Place the cursor right on top of that character?
Excel's "Find-and-Replace" doesnt work within the cell.

The macro I'm writing should massage the text in the following way:
1- (F2) Edit the text.
2- (How?) Find the minus sign, and position the cursor on top of it.
3- (Shft-Ctrl-End) Highlight all text from the minus sign to the end of
the cell.
4- (Ctrl-X) Cut the text.
5- (Tab, Ctrl-V) Move one cell to the right and paste the string.

Simple... I wish.

Thxs -
D.D.

D.D.

Check out this recent thread (from last week) on how to do it.

http://groups.google.com/group/micro...bfb047eb8daaeb

Thanks for the prompt reply. I'll mull over the code and make it work. I
was hoping for a HotKey...
It seems odd to you that Excel lacks such a simple, common-sense feature
like this (aka "Find-while-in-Edit-Mode")?

Thanks - Dick Dixon

"Greg Glynn" wrote:

D.D.

Check out this recent thread (from last week) on how to do it.

http://groups.google.com/group/micro...bfb047eb8daaeb

Whilst Greg's posted link will do what you want, do you really need
select/cut ?
You know what the result should be, without user intervention, so code the
changes in VBA.

Private Sub CommandButton1_Click()
Dim HyphenPos As Long

With ActiveCell
HyphenPos = InStr(1, .Value, "-")
If HyphenPos 0 Then
.Offset(0, 1).Value = Mid(.Value, HyphenPos + 1)
.Value = Left(.Value, HyphenPos - 1)
End If
End With

End Sub



"D.D." wrote in message
...
The Excel cell contains text with a "-" minus sign embedded in it (e.g. a
phone number).
Q: Is there a Hotkey that will
1) "Find" the minus sign, and
2) Place the cursor right on top of that character?
Excel's "Find-and-Replace" doesnt work within the cell.

The macro I'm writing should massage the text in the following way:
1- (F2) Edit the text.
2- (How?) Find the minus sign, and position the cursor on top of it.
3- (Shft-Ctrl-End) Highlight all text from the minus sign to the end of
the cell.
4- (Ctrl-X) Cut the text.
5- (Tab, Ctrl-V) Move one cell to the right and paste the string.

Simple... I wish.

Thxs -
D.D.

D.D. - Also consider using a formula in the cell to the right of the phone
number in A5:

=RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)+1) <<<includes the minus sign
=RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)) <<<excludes the minus sign

--
Jay


"D.D." wrote:

The Excel cell contains text with a "-" minus sign embedded in it (e.g. a
phone number).
Q: Is there a Hotkey that will
1) "Find" the minus sign, and
2) Place the cursor right on top of that character?
Excel's "Find-and-Replace" doesnt work within the cell.

The macro I'm writing should massage the text in the following way:
1- (F2) Edit the text.
2- (How?) Find the minus sign, and position the cursor on top of it.
3- (Shft-Ctrl-End) Highlight all text from the minus sign to the end of
the cell.
4- (Ctrl-X) Cut the text.
5- (Tab, Ctrl-V) Move one cell to the right and paste the string.

Simple... I wish.

Thxs -
D.D.

NickHK: You are correct. The expected outcome is known. I am a newbie at
VB but your code seems very compact, understandable and well within my
skillset. Thanks!

"NickHK" wrote:

Whilst Greg's posted link will do what you want, do you really need
select/cut ?
You know what the result should be, without user intervention, so code the
changes in VBA.

Private Sub CommandButton1_Click()
Dim HyphenPos As Long

With ActiveCell
HyphenPos = InStr(1, .Value, "-")
If HyphenPos 0 Then
.Offset(0, 1).Value = Mid(.Value, HyphenPos + 1)
.Value = Left(.Value, HyphenPos - 1)
End If
End With

End Sub



"D.D." wrote in message
...
The Excel cell contains text with a "-" minus sign embedded in it (e.g. a
phone number).
Q: Is there a Hotkey that will
1) "Find" the minus sign, and
2) Place the cursor right on top of that character?
Excel's "Find-and-Replace" doesnt work within the cell.

The macro I'm writing should massage the text in the following way:
1- (F2) Edit the text.
2- (How?) Find the minus sign, and position the cursor on top of it.
3- (Shft-Ctrl-End) Highlight all text from the minus sign to the end of
the cell.
4- (Ctrl-X) Cut the text.
5- (Tab, Ctrl-V) Move one cell to the right and paste the string.

Simple... I wish.

Thxs -
D.D.

Jay:
I am a BASIC programmer, but was unaware of the SEARCH function in VB. Your
solution is *really* compact, and effectively dodges the need to become VB
programmer in, say, the next 24 hours.... though it appears a mastry of VB is
inevitable! Thanks !

"Jay" wrote:

D.D. - Also consider using a formula in the cell to the right of the phone
number in A5:

=RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)+1) <<<includes the minus sign
=RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)) <<<excludes the minus sign

--
Jay


"D.D." wrote:

The Excel cell contains text with a "-" minus sign embedded in it (e.g. a
phone number).
Q: Is there a Hotkey that will
1) "Find" the minus sign, and
2) Place the cursor right on top of that character?
Excel's "Find-and-Replace" doesnt work within the cell.

The macro I'm writing should massage the text in the following way:
1- (F2) Edit the text.
2- (How?) Find the minus sign, and position the cursor on top of it.
3- (Shft-Ctrl-End) Highlight all text from the minus sign to the end of
the cell.
4- (Ctrl-X) Cut the text.
5- (Tab, Ctrl-V) Move one cell to the right and paste the string.

Simple... I wish.

Thxs -
D.D.