Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
Date Formatting in Excel Urgent | Excel Discussion (Misc queries) | |||
Complex cond. formatting...Pls. Help! | Excel Discussion (Misc queries) | |||
complex?? Q about Conditional formatting | Excel Worksheet Functions | |||
*URGENT* - Complex formula needed | Excel Discussion (Misc queries) |