Field Split Programming Questions

Posted on 2006-07-17
Last Modified: 2010-04-23
I have a text field that contains multiple Catagories each is identified by ';' semicolon following the catagory name as follows:

CATEGORY1;This text for catagory 1. CATAGORY2; This text for catagory 2. CATAGORY 3; This is text for catagory 3.

I need to separate each Catagory into separate fields as follows:

FLD1 = CATEGORY1;This text for catagory 1
FLD2 =  CATAGORY2; This text for catagory 2.
FLD 3 = CATAGORY 3; This is text for catagory 3.

Any suggestions on how I can do this?  I am currently using the split command but this is giving the following results:

FLD2 =  This text for catagory 1[][]CATAGORY2
FLD 3 = This text for catagory 2.[][]CATAGORY 3
FLD 4 = This is text for catagory 3.

Here is my code:

 For Each dr In dt.Rows
        tAuditComments = Convert.ToString(dr("TRIPLEMEMO")).ToString()
        Fields = tAuditComments.Split(";"c) +
Question by:Hojoformo
  • 4
  • 2
  • 2
  • +2
LVL 23

Expert Comment

ID: 17121515
Hi Hojoformo,

split by '.'

LVL 23

Expert Comment

ID: 17121527
You are splitting by ; but the lines are seperated by the fullstops by the looks of it

Author Comment

ID: 17121532
I can not split by period becuase there can be multiple sentences per catagory as follows:

CATEGORY1;This text for catagory 1. This more text for catagory 1. CATAGORY2; This text for catagory 2. CATAGORY 3; This is text for catagory 3.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 23

Expert Comment

ID: 17121547
ok, are you able to change the splitter symbol to something else, like a #, as there is no correlation to the splitting at the moment

CATEGORY1;This text for catagory 1. This more text for catagory 1#
CATAGORY2; This text for catagory 2#
CATAGORY 3; This is text for catagory 3#

then you can split on #
LVL 23

Assisted Solution

apresto earned 100 total points
ID: 17121557
also could you delete the duplicate question you have posted in otherwise the moderators will tell you to do so.  You can create a pointer question in the topic area pointing to this question, you can post multiople questions aslong as they do not exceed 500 points.  Just tellin gyou so that it saves the mods and clean up staff some time, as you can just hit the delete link :o)
LVL 24

Expert Comment

by:Jeff Certain
ID: 17121951
The approach that will work for your scenario is to find the index of CATEGORY and then find the index of the "." before it (using string.indexof). You'll then need to split the string using string.substring.

A word of caution... the first pass through will fail, since there is no . immediately before category1, so you'll need to accomodate this.

LVL 34

Accepted Solution

Sancler earned 400 total points
ID: 17122029
Try this

Imports System.Text.RegularExpressions

        Dim r As New Regex("CAT")
        Dim teststring As String = "CATEGORY1;This text for catagory 1. CATAGORY2; This text for catagory 2. CATAGORY 3; This is text for catagory 3."
        Dim parts() As String = r.Split(teststring)
        For Each part As String In parts
            Debug.WriteLine("CAT" & part)

This splits on CAT (specifically, upper case) using regex (because string.split will only take single characters and it doesn't look like a single character would be sufficient for your scenario).  But simple regex.split leaves out from the result the string used for the split, so this demo adds it back.  I'm sure there's more elegant ways of doing it with regex, but perhaps FernandoSoto - who's the king in this TA so far as Regex is concerned - will chip in with something better.

LVL 63

Expert Comment

by:Fernando Soto
ID: 17122077
Hi Hojoformo;

I have a couple of questions for you.

1. Is the Delimiter "CATAGORY" always in capital leters?
2. Is it always immediately followed by a numeric value, no white space characters? For example "CATAGORY99" and NOT "CATAGORY 99".
3. Are the lines terminated with a carrage return and line feed or is it one continuous line of data?

LVL 34

Expert Comment

ID: 17122331

I hoped we might see you ;-)

It may just be a typo, but I noted that, in the example, the first one was spelled with an "E" - CATEGORY - but the latter two with an "A" - CATAGORY.

LVL 63

Expert Comment

by:Fernando Soto
ID: 17122490
Hi Roger;

I like your solution to the problem. ;=)


Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question