new columns created from " "

I have a bunch of data in a column and it is "separated" by " ".

The data was imported from an online database.

Is there a way to create columns within the current workbook from where the
  appears? (much like importing a text file)

TIA,
Greg


0
jersey (1)
5/25/2004 3:22:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
579 Views

Similar Articles

[PageSpeed] 33

Greg Wilker wrote:
> I have a bunch of data in a column and it is "separated" by " ".
> 
> The data was imported from an online database.
> 
> Is there a way to create columns within the current workbook from where the
>   appears? (much like importing a text file)
> 
> TIA,
> Greg
> 
> 
Hi Greg

" " is a "fixed whitespace" character in HTML.

Personally I would write a class to read in the data.
Note: You'll have to delete all non-data lines from the file (you can 
leave the header in as long as they are also seperated by " ".

Below is some source code, if you need any help, just drop me a line.

Papparotti

<SourceCode>
Additionally you have to write Property Get statements to extract the 
Data from the array. something along the line like :

add a class module and name it clsImportFile

========== CLASS ===============================

Property Get Col1() as String
	Col1 = Trim(FieldsInLine(0))	
End Property

Dim clsImportFile As Integer
Dim Buffer As String
Dim FieldsInLine(7) As String ' change the number to the amount of 
columns in the file

Sub OpenIt(Path As String)
     clsImportFile = FreeFile
     If Path = "" Or UCase(Path) = "FALSE" Then
         MsgBox "Filename error"
     Else
         Open Path For Input As clsImportFile
     End If
End Sub

Sub CloseIt()
     Close clsImportFile
End Sub


Function moveToNextLine() As Boolean
Dim x As Long
Dim cPos As Long
Dim nPos As Long

     If Not EOF(clsImportFile) Then
         Line Input #clsImportFile, Buffer
         cPos = 1
         For x = 0 To UBound(FieldsInLine)
             nPos = InStr(cPos, Buffer, "&nsbp;")
             On Error Resume Next
             FieldsInLine(x) = Mid(Buffer, cPos, nPos - cPos)
             cPos = nPos + 1
         Next x
         moveToNextLine = True
     Else
         moveToNextLine = False
     End If

End Function

====== END CLASS ===============================

========== MODULE ==============================
add a module and name it like you want.

sub importWebFile()
Dim i as long
dim myFile as new clsImportFile

   workbooks.add

   myFile.openIt(application.getopenfilename("All Files (*.*),*.*"))

   i=0

   while myFile.movetonextline

     i=i+1

     cells(i,1)=myFile.Col1

   wend

   myFile.closeit

end sub

======= END MODULE ==============================
</SourceCode>
0
5/25/2004 5:32:15 PM
A non-macro approach.

Edit|replace
replace  &nbsp;
with     , (comma--or some other unique unused character)
replace all

Then select the column and do:
data|text to columns.
delimited
specify that unique character 

Greg Wilker wrote:
> 
> I have a bunch of data in a column and it is "separated" by "&nbsp;".
> 
> The data was imported from an online database.
> 
> Is there a way to create columns within the current workbook from where the
> &nbsp; appears? (much like importing a text file)
> 
> TIA,
> Greg

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/25/2004 10:38:04 PM
Reply:

Similar Artilces: