Breaking text file down into individual cell

oneway

New Member
Joined
Aug 23, 2006
Messages
23
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.

Code:
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

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

HTH,

Smitty
 

oneway

New Member
Joined
Aug 23, 2006
Messages
23
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Aug 23, 2006
Messages
23

ADVERTISEMENT

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
 

oneway

New Member
Joined
Aug 23, 2006
Messages
23
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

Legend
Joined
May 15, 2003
Messages
29,536
I'd read the specs in the article I referenced and see if the viewer supports MAC's. I don't know.

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,114,388
Messages
5,547,661
Members
410,805
Latest member
Ginoji
Top