Breaking text file down into individual cell

[oneway]

I will be importing 4 text files into a worksheet.
I'm doing this by a command button for each text file, ( the only way I could figure out adding multiple text file to one worksheet, there maybe a better way, but this work). Here is the code for one of the buttons, the other three are the same except for the file path and name.

Private Sub cmdcyan_Click()
Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\Documents and Settings\oneway\Desktop\cyan.txt"

  iFileNum = FreeFile
  Open sFile For Input As iFileNum
  Input #iFileNum, sText
  Close #iFileNum
  
If Cells(1, 1).Value = "" Then
   lRowNum = 1
Else
   lRowNum = ActiveSheet.UsedRange.Rows.Count + 1
End If
Cells(lRowNum, 1).Value = sText
End Sub

the text file contians 23 numbers with a separator ( this could be any of these: Colon, Simi Colon, Space, or a Comma). The file now just goes into one cell. I'd like to separate each number into a individual cell, is this possible?

 

[Smitty]

Welcome to the Board!

Have you tried Data-->Text to Columns? Just select all of the delimiters appropriate to your situation.

HTH,

Smitty

 

[oneway]

Thanks Smitty,

I've only been working with Excel off and on for about six months. I didn't know about the Data/Text to Columns. It's looks like a handy little function. But I see that I may worded my post wrong. I need to be able to put each of the 23 numbers on a separate row,running down the worksheet. I'd like this to be a function of the command button on the worksheet (not sure if function is the right term).

 

[Smitty]

Once you've parsed the information, you can copy it and goto Edit-->Paste Special-->Transpose to paste row data to columns & vice versa.

Smitty

 

[oneway]

I was able to get what I needed by using the =MID in each cell, may have not been the best way but it works.

Now one more question. Is there a way I can save this work sheet so someone with out excel can see worksheet like it is? This is part of the worksheet, I also have buttons above each coloum to add the number into the blank cells.

EDIT: Removed HTML - It was incomplete and screwing up the page - Smitty

 

[Smitty]

Is there a way I can save this work sheet so someone with out excel can see worksheet like it is?

You can have them get the Excel Viewer, which is free from Microsoft.

Smitty

 

[oneway]

Will that work on a Mac?
I was able to get it to save as a xlhtml, but not sure if they'll be able to see it ether.

 

[Smitty]

I'd read the specs in the article I referenced and see if the viewer supports MAC's. I don't know.

Smitty