VBS String manipulation

September 11, 2009 at 13:58:29
Specs: Windows XP
I have a text file with 5,000 words and definitions, that I want to drop into a table or Excel. Problem is, there are no commas or other delineators that would help me to drop the data into 3 columns, by word, part of speech, and definition. The lines of data are all different lengths.
Is it possible to program somehow to separate the data, by first space, then after that, by anything after the first period? My data looks like this:
vivacity n. Liveliness.
So, I want to put a comma or something after the y in vivacity (first space), then again, after the period after n, for the first 2 columns in Excel, and drop the rest into the third column.
Is this possible for a relative newbie?

See More: VBS String manipulation

Report •

September 11, 2009 at 15:21:10
It's easy enough to do, but I'd probably use VBS/VBA to load the data into Excel itself, and skip a step or two.

Report •

September 11, 2009 at 16:10:59
OK. Want to take a crack at it?

Report •

September 11, 2009 at 16:19:45
I could, but I'm not a "relative newbie," nor do I have any more free time today.

How about you take a crack at it, report back, and I'll give pointers/working code.

Report •

Related Solutions

September 12, 2009 at 07:46:29
Oh, hey. Look who had some time to kill?

This VBScript works with Excel 2003. It'll probably bomb with Excel 2000 and I assume Excel XP.

Const inName = "input.txt"
Set inFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(inName, 1)
Set excel = CreateObject("Excel.Application")

excel.Visible = True
excel.SheetsInNewWorkbook = 1
With excel.Workbooks
  Set cell = .Item(.Count).Worksheets(1).Cells(1)
End With

On Error Resume Next
excel.ScreenUpdating = False
Do Until inFile.AtEndOfStream
  line = Trim(inFile.ReadLine)
  div1 = InStr(line, " ")
  div2 = InStr(div1, line, ".")
  With cell
    .Formula = Left(line, div1 - 1)
    .Offset(0, 1) = Mid(line, div1 + 1, div2 - div1)
    .Offset(0, 2) = Trim(Mid(line, div2 + 1))
    Set cell = .Offset(1, 0)
  End With
excel.ScreenUpdating = True

If err.Number = 0 Then
  WScript.Echo "Done"
  WScript.Echo "Error: " & err.Number & ": " & err.Description
End If

Report •

September 18, 2009 at 15:59:46

A bit late, but look at the extended text functions here

These functions allow you to specify various delimiters for splitting text.

You can enter negative values so that you can get the text from the last space to the end. =xtndRight(A1, " ",-1)

In the extended mid formula =xtndmid(A1, ".",1," ",-1) for example,
you can use two different delimiters and each can be counted from the start or the end of your text.

So just drop all your text into Excel and use these functions to get the bits you want into a series of cells.

Delimiters can be any length, so once you get one word with one function you can use that word as the delimiter for the next function.


Report •

Ask Question