How to delimit a column into several columns in Access? (1 Viewer)

[hclifford]

Hi all,

I have an imported table and within this table contains a column that needs to be further delimited. I've read that it is possible to delimit columns using the left, right and mid functions, however, I need to delimit it to more than 3 columns. Maybe an estimated 6.

Examples of the data contained in the column are as follow:
ITM~W01GGASPAPP1B:W6400~12.34.56.78~~W01GGASPAPP1B~W01GGASPAPP1B~ACK~

ITM~a01gibapp3a:AC900~12.34.56.78~~a01gibapp3a~a01gibapp3a~ACK~

TEC~~01.234.567.89~~~~ACK~

I need to delimit it by "~". It must also be similar to Excel's text-to-column feature whereby if there're no values between 2 "~", it will be recorded as null.

The main thing I require from each record in the column is contained within the first and second "~" (even if it's null).

Is this possible? How do I go about doing this?

 

[MarkK]

Check out the VBA.Split() function, which returns an array of elements as delimited by a character you specify, so this . . .

var = Split("this~is an example~of vba.split()~", "~")

. . . will have elements . . .

var(0) = "this"
var(1) = "is an example"
var(2) = "of vba.split()"
var(3) = ""

 

[smig]

what Markk said.

just to note that two ~ in a row won't give you a null values but an empty string ""

No need to estimate. looking at the example you gave you have 8 variables. The last ~ will also create a variable.

 

[hclifford]

Thanks for the information and advice, Markk and Smig!

Managed to do it already