split a field (1 Viewer)

[jiajian]

Hi,

I have a field which has already contain the product name as :

EnchSG100ml,
EnchSG100ml+RomaSH50ml,
EnchSH250ml+RomaHB100ml,
RomaSG700ml+EnchSH120ml,
RomaHB250ml,

(Where Ench/Roma are product brand, SG/SH/HB are product type & ml is the product volume & "+" means the it banded together as a single product.)

I would like to split this fiels to 2 fields as Product1 & Product2, how should I do so?

I had tried with Right / Left function but it does not work for a records with "+"

Thanks

 

[raskew]

Hi,

The InStr() function will allow you to locate the position of the '+'.
With that, you can use left() and mid() functions to return portions
of the string. Here's an example in the debug window:

x = "EnchSG100ml+RomaSH50ml,"

? left(x, instr(x, "+")-1)
EnchSG100ml

? mid(x, instr(x, "+")+1)
RomaSH50ml,

HTH- Bob

 

[Mike375]

Here id 3 calculated query fields I use to clean up phone numbers and this case a - is the culprit. The field name CLPhHome is where the phone numbers are stored.

Exp2ab: InStr([CLPhHome],"-")

The results returned in the field Exp2ab is the character count where the - is found.

TestExpab: IIf([Exp2ab]>0,Mid([CLPhHome],([Exp2ab]+1),100),0)

Yoy can see the field TestExpab is using the results of Exp2ab and this one returns the numbers to the right of the -

TestExp2ab: IIf([Exp2ab]>0,Mid([CLPhHome],1,([Exp2ab]-1)),0)

This one returns the numbers to the left of the -

You can include several in a series. For example I have them for ( ) and a space

 

[nyoman]

Dim MyString
MyString = Split("EnchSG100ml+RomaSH50ml,", "+", -1, 1)
' MyString(0) contains "EnchSG100ml".
' MyString(1) contains "RomaSH50ml,".