ExcelBanter - Complex text to Excel formatting. Urgent

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Complex text to Excel formatting. Urgent (https://www.excelbanter.com/excel-worksheet-functions/209162-complex-text-excel-formatting-urgent.html)

Complex text to Excel formatting. Urgent

Hi experts. I am totally lost with the question below:
I have a list of 1000 sites are continous in txt format where i need to
transfer the info in Excel format.
Info about each site starts by LST ALD: and ends by [---- end]

Now i need to transfer and extract info from the txt and put it in excel as
below:
Site No Device Name Scenario etc...
Site1 2100_R_U1 Regular
Site2 2100_R_U2 Regluar
etc....
------------------------------------------------------------------------------
LST ALD: OPMODE=CSAT;
A30042
+++ Site1
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end

LST ALD: OPMODE=CSAT;
B30042
+++ Site2
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end
etc......

If you have some tips do not hesitate to send .......thanks

Complex text to Excel formatting. Urgent

If you are familiar with VBA then
1. Paste the text file into an Excel sheet in Col A
2. In B1 enter =Trim(A1) and copy down
3. Select col B, Cut and PASTE Special|Values over Col A
4. Run the macro below
Sub convert()

Dim lastRow As Long
Dim i, j, loopCount, startAt, endAt As Integer

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

loopCount = lastRow / 11
For i = 1 To loopCount

j = 3 + (i - 1) * 11
startAt = 6
endAt = Len(Cells(j, 1))
Cells(i, 4) = Mid(Cells(j, 1), startAt, endAt)

j = j + 4
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 5) = Mid(Cells(j, 1), startAt, endAt)

j = j + 1
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 6) = Mid(Cells(j, 1), startAt, endAt)
Next
End Sub

5. If you have more fields to pick up then add to the macro above, as
appropirate

Assumption:
Each records has 11 rows in the txt file
You need to pick up row 3, 7 and 8
Value in row 3 starts after "+++" (at 5th position)
Value in row 7 and 8 starts after "="
Output is written on the same sheet starting at D1

"Teddy" wrote:

Hi experts. I am totally lost with the question below:
I have a list of 1000 sites are continous in txt format where i need to
transfer the info in Excel format.
Info about each site starts by LST ALD: and ends by [---- end]

Now i need to transfer and extract info from the txt and put it in excel as
below:
Site No Device Name Scenario etc...
Site1 2100_R_U1 Regular
Site2 2100_R_U2 Regluar
etc....
------------------------------------------------------------------------------
LST ALD: OPMODE=CSAT;
A30042
+++ Site1
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end

LST ALD: OPMODE=CSAT;
B30042
+++ Site2
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end
etc......

If you have some tips do not hesitate to send .......thanks

Complex text to Excel formatting. Urgent

Hi Sheeloo, thanks for the help. Very encouraging and wanna learn more.
Another small question is that each record is not exactly even(11counts) .To
avoid unexpected cases might occur i was looking more to make a search
between LST ALD and End , search & extract values with parameter Device name
and Senario etc..wonder if it makes it more complicated?

"Sheeloo" wrote:

If you are familiar with VBA then
1. Paste the text file into an Excel sheet in Col A
2. In B1 enter =Trim(A1) and copy down
3. Select col B, Cut and PASTE Special|Values over Col A
4. Run the macro below
Sub convert()

Dim lastRow As Long
Dim i, j, loopCount, startAt, endAt As Integer

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

loopCount = lastRow / 11
For i = 1 To loopCount

j = 3 + (i - 1) * 11
startAt = 6
endAt = Len(Cells(j, 1))
Cells(i, 4) = Mid(Cells(j, 1), startAt, endAt)

j = j + 4
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 5) = Mid(Cells(j, 1), startAt, endAt)

j = j + 1
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 6) = Mid(Cells(j, 1), startAt, endAt)
Next
End Sub

5. If you have more fields to pick up then add to the macro above, as
appropirate

Assumption:
Each records has 11 rows in the txt file
You need to pick up row 3, 7 and 8
Value in row 3 starts after "+++" (at 5th position)
Value in row 7 and 8 starts after "="
Output is written on the same sheet starting at D1

"Teddy" wrote:

Hi experts. I am totally lost with the question below:
I have a list of 1000 sites are continous in txt format where i need to
transfer the info in Excel format.
Info about each site starts by LST ALD: and ends by [---- end]

Now i need to transfer and extract info from the txt and put it in excel as
below:
Site No Device Name Scenario etc...
Site1 2100_R_U1 Regular
Site2 2100_R_U2 Regluar
etc....
------------------------------------------------------------------------------
LST ALD: OPMODE=CSAT;
A30042
+++ Site1
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end

LST ALD: OPMODE=CSAT;
B30042
+++ Site2
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end
etc......

If you have some tips do not hesitate to send .......thanks

Complex text to Excel formatting. Urgent

Yes, it can be done.

Let me know if you want me to do it. Also let me know which fields you want
to pick up and in which column...

If possible, send me the text file and Excel file in which you want the
output.

My id is to_sheeloo
add @hotmail.com to the id to get my email address.

WHat is your deadline?

"Teddy" wrote:

Hi Sheeloo, thanks for the help. Very encouraging and wanna learn more.
Another small question is that each record is not exactly even(11counts) .To
avoid unexpected cases might occur i was looking more to make a search
between LST ALD and End , search & extract values with parameter Device name
and Senario etc..wonder if it makes it more complicated?

"Sheeloo" wrote:

If you are familiar with VBA then
1. Paste the text file into an Excel sheet in Col A
2. In B1 enter =Trim(A1) and copy down
3. Select col B, Cut and PASTE Special|Values over Col A
4. Run the macro below
Sub convert()

Dim lastRow As Long
Dim i, j, loopCount, startAt, endAt As Integer

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

loopCount = lastRow / 11
For i = 1 To loopCount

j = 3 + (i - 1) * 11
startAt = 6
endAt = Len(Cells(j, 1))
Cells(i, 4) = Mid(Cells(j, 1), startAt, endAt)

j = j + 4
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 5) = Mid(Cells(j, 1), startAt, endAt)

j = j + 1
startAt = WorksheetFunction.Find("=", Cells(j, 1)) + 1
endAt = Len(Cells(j, 1))
Cells(i, 6) = Mid(Cells(j, 1), startAt, endAt)
Next
End Sub

5. If you have more fields to pick up then add to the macro above, as
appropirate

Assumption:
Each records has 11 rows in the txt file
You need to pick up row 3, 7 and 8
Value in row 3 starts after "+++" (at 5th position)
Value in row 7 and 8 starts after "="
Output is written on the same sheet starting at D1

"Teddy" wrote:

Hi experts. I am totally lost with the question below:
I have a list of 1000 sites are continous in txt format where i need to
transfer the info in Excel format.
Info about each site starts by LST ALD: and ends by [---- end]

Now i need to transfer and extract info from the txt and put it in excel as
below:
Site No Device Name Scenario etc...
Site1 2100_R_U1 Regular
Site2 2100_R_U2 Regluar
etc....
------------------------------------------------------------------------------
LST ALD: OPMODE=CSAT;
A30042
+++ Site1
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end

LST ALD: OPMODE=CSAT;
B30042
+++ Site2
O&M #20062
Antenna Line Device Configure
-----------------------------
Device Name = UMTS2100_RET_U1
Scenario = REGULAR
Site No. = 0
---- end
etc......

If you have some tips do not hesitate to send .......thanks