Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
I have a large text file for import. I need some ideas to combine to line of strings (text) Below is the sample of text Before sample (1) aaaaaaa bbbbbbbbbbbbbbbb sample (2) ccccccccccccc dddddddd After Sample (1) aaaaaaa bbbbbbbbbbbbbbbb Sample (2) ccccccccccccc dddddddd I just need the vba code to join the text. Any inputs is highly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi Norhaya
You need to give a little more info on how your sample are delimited in the text file (i.e. are they comma delimited or tab delimited or may you have a disk file). If you already have that info, then to join two text strings just use the ampersand operator as follows: Sub JoinTXT() Dim String1 As String Dim String2 As String Dim String3 As String String3 = String1 & String2 End Sub If this helps please click "Yes" <<<<<<<<<< "norhaya" wrote: Hi I have a large text file for import. I need some ideas to combine to line of strings (text) Below is the sample of text Before sample (1) aaaaaaa bbbbbbbbbbbbbbbb sample (2) ccccccccccccc dddddddd After Sample (1) aaaaaaa bbbbbbbbbbbbbbbb Sample (2) ccccccccccccc dddddddd I just need the vba code to join the text. Any inputs is highly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
Below is the actual text file which I want to work on. I want to join the text line situated above the text line that contain "Category" it will become one line. 2nd, I want to join the 1 line below the text line which contain "-- Total quantities " -------------------------------------------------------------- Date: 05 Oct 09 7:51am Page: 1 Item Status Report Items selected with the control account set (All account sets ). Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ] Item Number Description 47 LAMP Category [CON] Tax status [0] Markup factor [ 1.00 ] Last shipment [ ] Picking sequence [ ] Stock item [Y] Serial numbers [0] Last receipt [07 Mar 08] Report group [ ] Unit weight [ 0. 00 ] Avg.days between ship.[ 0 ] Average units shipped [ 0.00 ] EA -- Price per EA ------------- Previous price -- - Date of change ------------------------------------- Base price [ 0.00 ] [ 0.00 ] [29/11/05] Sale price [ 0.00 ] Sale starts [ / / ] ends [ / / ] -- Type - Percent -- Selling price based on (discount). A [ 0.00 ] Discount on price by (percentage). B [ 0.00 ] Pricing determined by (customer type ). C [ 0.00 ] D [ 0.00 ] E [ 0.00 ] -- Cost per EA -------------- Previous cost --- - Date of change ------------------------------------- Standard cost [ 0.00 ] [ 0.00 ] [29/11/05] Cost 1 [ 0.00 ] Most recent cost [ 0.47 ] [ 0.00 ] [07/03/08] Cost 2 [ 0.00 ] Account set (Consumables ) Costing method (Moving average ) Total cost [ 23.50 ] Average unit cost [ 0.47 ] EA -- Units of measure ----------------------------------- ------------------------------------------------------ Stocking unit [EA ] Pricing unit: (EA ) Costing unit: (EA ) Alternate units [ ] [ ] [ ] [ ] Conversion factors [ 1.0000 ] [ 1. 0000 ] [ 1.0000 ] [ 1.0000 ] -- Total quantities ---- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ 50.00 0.00 0.00 EA -- Quantities by loc. -- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ SEL 50.00 0.00 0.00 EA -- Reorder ----------- Minimum level -- Maximum level - --- Reorder qty ---------- Projected sales ----------- 01 Jan 09 0.00 0.00 0.00 EA 0.00 EA ------------------------------------------------------------------------------ --------------------------------------------- BSc Chem Eng Rick wrote: Hi Norhaya You need to give a little more info on how your sample are delimited in the text file (i.e. are they comma delimited or tab delimited or may you have a disk file). If you already have that info, then to join two text strings just use the ampersand operator as follows: Sub JoinTXT() Dim String1 As String Dim String2 As String Dim String3 As String String3 = String1 & String2 End Sub If this helps please click "Yes" <<<<<<<<<< Hi [quoted text clipped - 20 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
I'm sorry but I just can't understand the file as it is printed. If this is a
normal ".txt" file you have a bit of work ahead to first write a function which finds some or other marker text (like "Category"). Then you need to specify how far the program joins consecutive strings. If you post samples of your code I will help you to debug. "norhaya via OfficeKB.com" wrote: Hi Below is the actual text file which I want to work on. I want to join the text line situated above the text line that contain "Category" it will become one line. 2nd, I want to join the 1 line below the text line which contain "-- Total quantities " -------------------------------------------------------------- Date: 05 Oct 09 7:51am Page: 1 Item Status Report Items selected with the control account set (All account sets ). Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ] Item Number Description 47 LAMP Category [CON] Tax status [0] Markup factor [ 1.00 ] Last shipment [ ] Picking sequence [ ] Stock item [Y] Serial numbers [0] Last receipt [07 Mar 08] Report group [ ] Unit weight [ 0. 00 ] Avg.days between ship.[ 0 ] Average units shipped [ 0.00 ] EA -- Price per EA ------------- Previous price -- - Date of change ------------------------------------- Base price [ 0.00 ] [ 0.00 ] [29/11/05] Sale price [ 0.00 ] Sale starts [ / / ] ends [ / / ] -- Type - Percent -- Selling price based on (discount). A [ 0.00 ] Discount on price by (percentage). B [ 0.00 ] Pricing determined by (customer type ). C [ 0.00 ] D [ 0.00 ] E [ 0.00 ] -- Cost per EA -------------- Previous cost --- - Date of change ------------------------------------- Standard cost [ 0.00 ] [ 0.00 ] [29/11/05] Cost 1 [ 0.00 ] Most recent cost [ 0.47 ] [ 0.00 ] [07/03/08] Cost 2 [ 0.00 ] Account set (Consumables ) Costing method (Moving average ) Total cost [ 23.50 ] Average unit cost [ 0.47 ] EA -- Units of measure ----------------------------------- ------------------------------------------------------ Stocking unit [EA ] Pricing unit: (EA ) Costing unit: (EA ) Alternate units [ ] [ ] [ ] [ ] Conversion factors [ 1.0000 ] [ 1. 0000 ] [ 1.0000 ] [ 1.0000 ] -- Total quantities ---- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ 50.00 0.00 0.00 EA -- Quantities by loc. -- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ SEL 50.00 0.00 0.00 EA -- Reorder ----------- Minimum level -- Maximum level - --- Reorder qty ---------- Projected sales ----------- 01 Jan 09 0.00 0.00 0.00 EA 0.00 EA ------------------------------------------------------------------------------ --------------------------------------------- BSc Chem Eng Rick wrote: Hi Norhaya You need to give a little more info on how your sample are delimited in the text file (i.e. are they comma delimited or tab delimited or may you have a disk file). If you already have that info, then to join two text strings just use the ampersand operator as follows: Sub JoinTXT() Dim String1 As String Dim String2 As String Dim String3 As String String3 = String1 & String2 End Sub If this helps please click "Yes" <<<<<<<<<< Hi [quoted text clipped - 20 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
I'm very sure that the op asked this same question quite recently.
I believe my response was that parsing a textfile of this complexity was simply too difficult a task and that the output file should be simpler, such as a CSV. there are simply too many problems with the file that is used now. "BSc Chem Eng Rick" wrote: I'm sorry but I just can't understand the file as it is printed. If this is a normal ".txt" file you have a bit of work ahead to first write a function which finds some or other marker text (like "Category"). Then you need to specify how far the program joins consecutive strings. If you post samples of your code I will help you to debug. "norhaya via OfficeKB.com" wrote: Hi Below is the actual text file which I want to work on. I want to join the text line situated above the text line that contain "Category" it will become one line. 2nd, I want to join the 1 line below the text line which contain "-- Total quantities " -------------------------------------------------------------- Date: 05 Oct 09 7:51am Page: 1 Item Status Report Items selected with the control account set (All account sets ). Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ] Item Number Description 47 LAMP Category [CON] Tax status [0] Markup factor [ 1.00 ] Last shipment [ ] Picking sequence [ ] Stock item [Y] Serial numbers [0] Last receipt [07 Mar 08] Report group [ ] Unit weight [ 0. 00 ] Avg.days between ship.[ 0 ] Average units shipped [ 0.00 ] EA -- Price per EA ------------- Previous price -- - Date of change ------------------------------------- Base price [ 0.00 ] [ 0.00 ] [29/11/05] Sale price [ 0.00 ] Sale starts [ / / ] ends [ / / ] -- Type - Percent -- Selling price based on (discount). A [ 0.00 ] Discount on price by (percentage). B [ 0.00 ] Pricing determined by (customer type ). C [ 0.00 ] D [ 0.00 ] E [ 0.00 ] -- Cost per EA -------------- Previous cost --- - Date of change ------------------------------------- Standard cost [ 0.00 ] [ 0.00 ] [29/11/05] Cost 1 [ 0.00 ] Most recent cost [ 0.47 ] [ 0.00 ] [07/03/08] Cost 2 [ 0.00 ] Account set (Consumables ) Costing method (Moving average ) Total cost [ 23.50 ] Average unit cost [ 0.47 ] EA -- Units of measure ----------------------------------- ------------------------------------------------------ Stocking unit [EA ] Pricing unit: (EA ) Costing unit: (EA ) Alternate units [ ] [ ] [ ] [ ] Conversion factors [ 1.0000 ] [ 1. 0000 ] [ 1.0000 ] [ 1.0000 ] -- Total quantities ---- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ 50.00 0.00 0.00 EA -- Quantities by loc. -- Qty on hand ------ Qty on P/O ------ Qty on S/O ------------------------------------ SEL 50.00 0.00 0.00 EA -- Reorder ----------- Minimum level -- Maximum level - --- Reorder qty ---------- Projected sales ----------- 01 Jan 09 0.00 0.00 0.00 EA 0.00 EA ------------------------------------------------------------------------------ --------------------------------------------- BSc Chem Eng Rick wrote: Hi Norhaya You need to give a little more info on how your sample are delimited in the text file (i.e. are they comma delimited or tab delimited or may you have a disk file). If you already have that info, then to join two text strings just use the ampersand operator as follows: Sub JoinTXT() Dim String1 As String Dim String2 As String Dim String3 As String String3 = String1 & String2 End Sub If this helps please click "Yes" <<<<<<<<<< Hi [quoted text clipped - 20 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
Maybe, you help me on the following text, this is simply a txt file format which I download from our systems. I've done the simple parse but I need to do so many steps. that's why I thought there might be a shorter solution.. 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 After join, the text should read as follows, there's not need to eliminate the spaces etc.... 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 if I can do just that join in the text file, then I can import the data easy. if you hv something similar could you please direct me to that link. I've been searching in the next but could find anything close. Patrick Molloy wrote: I'm very sure that the op asked this same question quite recently. I believe my response was that parsing a textfile of this complexity was simply too difficult a task and that the output file should be simpler, such as a CSV. there are simply too many problems with the file that is used now. I'm sorry but I just can't understand the file as it is printed. If this is a normal ".txt" file you have a bit of work ahead to first write a function [quoted text clipped - 104 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200910/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
Anyone can assist me on this.........I really appreciate it very much norhaya wrote: Hi Maybe, you help me on the following text, this is simply a txt file format which I download from our systems. I've done the simple parse but I need to do so many steps. that's why I thought there might be a shorter solution.. 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 After join, the text should read as follows, there's not need to eliminate the spaces etc.... 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 if I can do just that join in the text file, then I can import the data easy. if you hv something similar could you please direct me to that link. I've been searching in the next but could find anything close. I'm very sure that the op asked this same question quite recently. [quoted text clipped - 8 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
Anyone can assist me on this.........I really appreciate it very much norhaya wrote: Hi Maybe, you help me on the following text, this is simply a txt file format which I download from our systems. I've done the simple parse but I need to do so many steps. that's why I thought there might be a shorter solution.. 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 After join, the text should read as follows, there's not need to eliminate the spaces etc.... 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 if I can do just that join in the text file, then I can import the data easy. if you hv something similar could you please direct me to that link. I've been searching in the next but could find anything close. I'm very sure that the op asked this same question quite recently. [quoted text clipped - 8 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
To join 1st line & 2nd line of text in text in Excel VBA Code
Hi
Anyone can assist me on this.........I really appreciate it very much norhaya wrote: Hi Maybe, you help me on the following text, this is simply a txt file format which I download from our systems. I've done the simple parse but I need to do so many steps. that's why I thought there might be a shorter solution.. 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 After join, the text should read as follows, there's not need to eliminate the spaces etc.... 47 LAMP Category -- Total quantities ---- Qty on hand 50.00 if I can do just that join in the text file, then I can import the data easy. if you hv something similar could you please direct me to that link. I've been searching in the next but could find anything close. I'm very sure that the op asked this same question quite recently. [quoted text clipped - 8 lines] I just need the vba code to join the text. Any inputs is highly appreciated. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Programming | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
Moving text from a line to a new line based on a condition | Excel Programming | |||
Looking for code to separate one line of text into multiple lines in Excel | Excel Worksheet Functions | |||
A 2 line text showing up in the Cell in Excel prints in 1 line | Excel Discussion (Misc queries) |