Need help to extract characters

[Kumaras]

Hi all,
I need help in excel, I have these data in the first column,
TR000000ADC Display Budget
TT000000A3_DC Overtime statement
TU000000B4321 Display Call out time

I want to make it into two columns and extract only the last few
characters from the first column and make the final document look like
below

ADC | Display Budget
A3DC | Overtime statement
B4321 | Display Call out time

Can anybody provide help in this regard, I want this in macro as the
data is 150 rows...thanks

Rgds

Ajit

 

[Bill Renaud]

Turn on the macro recorder and use Data|Text to Columns command (choose the
Fixed Width option). Skip importing the first 8 characters.

Sub ParseData()
Selection.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), _
Array(8, 1), _
Array(15, 1))
End Sub

For better readability, change the numbers in the FieldInfo argument back
to symbolic constants (see the xlColumnDataType class in the Object
Browser):

Sub ParseData2()
Selection.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, xlSkipColumn), _
Array(8, xlGeneralFormat), _
Array(15, xlGeneralFormat))
End Sub

 

[Bill Renaud]

I forgot to mention that the TextToColumns method is another one of those
methods that requires that you specify most ALL of the parameters to insure
that it works properly. If a user has manually used the Data|Text to
columns command prior to running your macro and has set some of these
properties to other values, then your macro will use those values by
default. This will cause your macro to malfunction.

Microsoft forgot to document this behavior (at least in Excel 2000). It is
similar to the Find and Replace commands (see the Remarks at the bottom of
these methods in Microsoft Excel Visual Basic Reference).

 

[Kumaras]

On Oct 10, 10:37 am, "Bill Renaud" <[email protected]>
wrote:
Hi Bill
Thanks for your help and wonders,,,,, It worked ....

Take care