Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to extract characters
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to extract characters
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 -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to extract characters
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). -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to extract characters
On Oct 10, 10:37 am, "Bill Renaud"
wrote: Hi Bill Thanks for your help and wonders,,,,, It worked .... Take care |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract last 2 characters from the right? | Excel Worksheet Functions | |||
Extract characters from a cell | Excel Discussion (Misc queries) | |||
Extract the first few characters | Excel Worksheet Functions | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
Extract Characters | Excel Programming |