Slicing and Dicing 1 Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Justin - 07 Dec 2006 17:39 GMT

So I want to take information that I get via a web query in Excel and
break it up.
The data currently looks like this:
InternalIP:Port ExternalIP:80 OUT masqueraded to FirewalIP:29176
timeout 6555
I would like to have each column give me this data:
InternalIP
InternalIP Port
External IP
In/Out
FirewallIP Port

The first part is easy, I can just do left(13) or so. But I would
rather do this programmatically so that it automatically updates
spreadsheet B with the new data after spreadsheet A does an import.
Ideally, I would like it to run a DNS lookup as well.

Any thoughts? If it's easier, I might just write a program to do it.

Reply to this Message

paul.robinson@it-tallaght.ie - 07 Dec 2006 18:00 GMT

Hi
This will parse out the four text strings ether side of the colons:
Dim TextStrings(1 to 4) as String
Temp = QueryString 'your query output
For i = 1 to 3
TextStrings(i) = Left(Temp, Instr(Temp, ":")-1)
Temp = Right(Temp,Len(Temp)-Instr(Temp, ":"))
next i
TextStrings(4) = Temp

You could do the same thing with each TextStrings(i) based on the " "
rather than the ":". Now extract the bits you want.
regards
Paul

> So I want to take information that I get via a web query in Excel and
> break it up.
[quoted text clipped - 14 lines]
>
> Any thoughts? If it's easier, I might just write a program to do it.

Reply to this Message

NickHK - 08 Dec 2006 02:56 GMT

Justin,
How far does TextToColumns get you ?
Or look into Regular Expressions.
http://visualbasic.about.com/od/usingvbnet/l/blregexa.htm

You want to resolve a host name from an IP address ?
http://vbnet.mvps.org/code/network/hostnamefromip.htm

NickHK

> So I want to take information that I get via a web query in Excel and
> break it up.
[quoted text clipped - 14 lines]
>
> Any thoughts? If it's easier, I might just write a program to do it.

Reply to this Message

Justin - 13 Dec 2006 15:22 GMT

TextToColumns gets me a #VALUE error.
The code is:

Public Function Text2Columns(ByVal input2use As Range)
Dim output As Range
output = Range("NATDump!B1")
input2use.TextToColumns output, , , , , , , True
End Function

> Justin,
> How far does TextToColumns get you ?
[quoted text clipped - 24 lines]
> >
> > Any thoughts? If it's easier, I might just write a program to do it.

Reply to this Message

NickHK - 14 Dec 2006 02:28 GMT

Justin,
Not sure how you are using that code, but recoding a macro will give you the
arguments.

NickHK
P.S. Your function does not return a value, which is all worksheet functions
can do.

> TextToColumns gets me a #VALUE error.
> The code is:
[quoted text clipped - 33 lines]
> > >
> > > Any thoughts? If it's easier, I might just write a program to do it.

Reply to this Message