Trim

[Guest]

In column A, I have texts like "XXX(000)" and I want to move or copy only
"000" to Column B and "XXX" left in column A.

Could someone please help. Thank you in advance.

 

[Guest]

Try this:


Select the range of values

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width.............Click [Next]
Put break points before and after the first parenthesis and before the last
Click [Next]
In the Step_3_of_3 page
Select column_2.........Check: Do not import
Select column_3.........Check: Text (only if you want to retain leading zeros)
Select column_4.........Check: Do not import
Click [Finish]............. to break the values into 2 columns

In my testing,
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Hello Ron,

Thank you for your reply first. in fact, I want to do it with Excel VBA
method. Could you please show me again.



"Ron Coderre" 來函：

Try this:


Select the range of values

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width.............Click [Next]
Put break points before and after the first parenthesis and before the last
Click [Next]
In the Step_3_of_3 page
Select column_2.........Check: Do not import
Select column_3.........Check: Text (only if you want to retain leading zeros)
Select column_4.........Check: Do not import
Click [Finish]............. to break the values into 2 columns

In my testing,
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

In column A, I have texts like "XXX(000)" and I want to move or copy only
"000" to Column B and "XXX" left in column A.

Could someone please help. Thank you in advance.

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iPos As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1
iPos = InStr(.Cells(i, TEST_COLUMN).Value, "(")
If iPos > 0 Then
.Cells(i, TEST_COLUMN).NumberFormat = "@"
.Cells(i, TEST_COLUMN).Offset(0, 1).Value = _
Replace(Right$(.Cells(i, TEST_COLUMN).Value,
Len(.Cells(i, TEST_COLUMN).Value) - iPos), ")", "")

.Cells(i, TEST_COLUMN).Value = Left$(.Cells(i,
TEST_COLUMN).Value, iPos - 1)
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello Ron,

Thank you for your reply first. in fact, I want to do it with Excel VBA
method. Could you please show me again.



"Ron Coderre" ??:

Try this:


Select the range of values

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width.............Click [Next]
Put break points before and after the first parenthesis and before the
last
Click [Next]
In the Step_3_of_3 page
Select column_2.........Check: Do not import
Select column_3.........Check: Text (only if you want to retain leading
zeros)
Select column_4.........Check: Do not import
Click [Finish]............. to break the values into 2 columns

In my testing,
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

In column A, I have texts like "XXX(000)" and I want to move or copy
only
"000" to Column B and "XXX" left in column A.

Could someone please help. Thank you in advance.

 

[Gord Dibben]

Turn on the Macro Recorder before selecting the range of values.

Follow Ron's steps from that point.


Gord Dibben MS Excel MVP

Hello Ron,

Thank you for your reply first. in fact, I want to do it with Excel VBA
method. Could you please show me again.



"Ron Coderre" ???

Try this:


Select the range of values

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width.............Click [Next]
Put break points before and after the first parenthesis and before the last
Click [Next]
In the Step_3_of_3 page
Select column_2.........Check: Do not import
Select column_3.........Check: Text (only if you want to retain leading zeros)
Select column_4.........Check: Do not import
Click [Finish]............. to break the values into 2 columns

In my testing,
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004
XXX(000) Becomes: XXX 000
abc(123) Becomes: abc 123
abc(004) Becomes: abc 004

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

In column A, I have texts like "XXX(000)" and I want to move or copy only
"000" to Column B and "XXX" left in column A.

Could someone please help. Thank you in advance.

 

[Helmut Weber]

Hi Alan,

i'm just fooling around with Excel and VBA:

' ---------------------------------------------------------
Public Sub ProcessDataB()
Dim allRows As Long ' all rows
Dim thisRow As Long ' this row
Dim thisClm As Long ' this column
thisClm = ActiveCell.Column ' beware what cell is selectd
With ActiveSheet
allRows = .Cells(.Rows.Count, thisClm).End(xlUp).Row
For thisRow = 1 To allRows
.Cells(thisRow, thisClm + 1).Formula = _
strBetween(.Cells(thisRow, thisClm), "(", ")")
.Cells(thisRow, thisClm).Formula = _
strBefore(.Cells(thisRow, thisClm), "(")
Next
End With
End Sub
' --------------------------------------------------------
Public Function strBetween(sTmp As String, x1 As String, x2 As String)
As String
Dim p1 As Long
Dim p2 As Long
p1 = InStr(sTmp, x1) + 1
p2 = InStr(sTmp, x2)
strBetween = Mid(sTmp, p1, p2 - p1)
End Function
' ---------------------------------------------------------
Public Function strBefore(sTmp As String, x1 As String) As String
Dim p1 As Long
p1 = InStr(sTmp, x1)
strBefore = Mid(sTmp, 1, p1 - 1)
End Function
' ---------------------------------------------------------

 

[Ron Rosenfeld]

In column A, I have texts like "XXX(000)" and I want to move or copy only
"000" to Column B and "XXX" left in column A.

Could someone please help. Thank you in advance.

In VBA, you could try something like this:

==================================
Option Explicit
Sub foo()
Dim c As Range
Dim str() As String

On Error Resume Next
For Each c In Selection
str = Split(c.Text, "(")
With c
.Value = str(0)
.Offset(0, 1) = Left(str(1), Len(str(1)) - 1)
.Offset(0, 1).NumberFormat = "@"
End With
Next c
End Sub
===============================
--ron