To join 1st line & 2nd line of text in text in Excel VBA Code

[norhaya]

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.

 

[BSc Chem Eng Rick]

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 via OfficeKB.com]

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
------------------------------------------------------------------------------
---------------------------------------------

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.

 

[BSc Chem Eng Rick]

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.

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
------------------------------------------------------------------------------
---------------------------------------------

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.

 

[Patrick Molloy]

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
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.

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
------------------------------------------------------------------------------
---------------------------------------------

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.

 

[norhaya via OfficeKB.com]

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.

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]

 

[norhaya via OfficeKB.com]

Hi

Anyone can assist me on this.........I really appreciate it very much

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]

 

[norhaya via OfficeKB.com]

Hi

Anyone can assist me on this.........I really appreciate it very much

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]

 

[norhaya via OfficeKB.com]

Hi

Anyone can assist me on this.........I really appreciate it very much

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]