+ Start a Discussion
Hector DiazHector Diaz 

Parse text fields

Hi
i have a text field separate by semicolon, for example

word1;word2;word3;word4;word5

and i want a custom formula field for each word, for example

field A  populated by word1
field B  populated by word2
field C  populated by word3
field D  populated by word4
field E  populated by word5

i have these formula, but only works with one semicolon
LEFT(textfield__c, (FIND(",", textfield__c)-1))

RIGHT(textfield__c, (LEN(textfield__c) – FIND(",", textfield__c)))


Thanks for your help


Best Answer chosen by Hector Diaz
Blessy Voola 4Blessy Voola 4
Hi Diaz

Try this for text5__c

right(Text__c,len(Text__c)-find(';',Text__c,find(text4__c,Text__c)))

Thanks
Please mark this answer as best if this resolves the issue.

All Answers

Gaurav NirwalGaurav Nirwal
Generic CSV Parser, Batchable:-

global with sharing class RecordProcessor implements Database.Stateful, Database.Batchable<DatabaseRecord>, Iterable<DatabaseRecord>, Iterator<DatabaseRecord> {
    global RecordProcessor(String csv) {
        data = csv.split('\n');
        Pattern endPattern   = Pattern.compile(',".+[^"]$'),
                startPattern = Pattern.compile('^[^"]+,"');
        for(integer idx = 0; idx < data.size()-1; idx++) {
            Matcher endMatch   =   endPattern.matcher(data[idx]  ),
                    startMatch = startPattern.matcher(data[idx+1]);
            if(endMatch.matches() && startMatch.matches()) {
                data[idx] += data.remove(idx+1);
                idx--;
            }
        }
        headers = data.remove(0).removeStart('"').removeEnd('"').split('"?,"?');
    }

    string[] data, headers;
    global class DatabaseRecord {
        global DatabaseRecord(String[] headers, String data) {
            fields = new Map<String, String>();
            String[] hdrs = headers.clone(), columns = data.split(',');
            for(Integer idx = 0; idx < columns.size() - 1; idx++) {
                if(columns[idx].startsWith('"') && !columns[idx].endsWith('"')) {
                    columns[idx] += columns.remove(idx+1);
                    idx--;
                }
            }
            while(!hdrs.isempty() && !columns.isempty()) {
                fields.put(hdrs.remove(0), columns.remove(0));
            }
        }
        Map<String, String> fields;
    }

    global Boolean hasNext() {
        return !data.isempty();
    }

    global DatabaseRecord next() {
        return new DatabaseRecord(headers, data.remove(0));
    }

    global Iterator<DatabaseRecord> iterator() {
        return this;
    }

    global Iterable<DatabaseRecord> start(Database.BatchableContext BC) {
        return this;
    }

    global void execute(Database.BatchableContext BC, DatabaseRecord[] records) {
        // Process the records here
    }

    global void finish(Database.BatchableContext bc) {
        // Report any errors here
    }
}


Gaurav NirwalGaurav Nirwal
If my answer can solves your problem then select it as best answer 
Hector DiazHector Diaz
Hi Matthews
tks for your answer,maybe a batch it's not good idea becuse these kind of fields such as word1,word2,word3,word4,word5 are not very frecuent in my org.
best,
Blessy Voola 4Blessy Voola 4

 Hi Diaz,

considering textfield__C as text__C
field A text1__C
field B  text2__C
field C  text3__C
field D  text4__C
field E  text5__C

formula for text1__c is left(text__c,find(';',text__c)-1)

formula for text2__c is left(trim(substitute(text__c,text1__c+';',' ')),find(';',trim(substitute(text__c,text1__c+';',' ')))-1)

formula for text3__c is left(trim(substitute(text__c,text1__c+';'+text2__c+';',' ')),find(';',trim(substitute(text__c,text1__c+';'+text2__c+';',' ')))-1)

formula for text4__c is  left(trim(substitute(text__c,text1__c+';'+text2__c+';'+text3__c+';',' ')),find(';',trim(substitute(text__c,text1__c+';'+text2__c+';'+text3__c+';',' ')))-1)

formula for text5__c is  trim(substitute(text__c,text1__c+';'+text2__c+';'+text3__c+';'+text4__c+';',' '))

Hope this works. Thanks!

Ettore DiazEttore Diaz
Hi Blessy, tks in advance for your help, when i checked the syntax in the last formula (text5_c) the Compiled formula is too big to execute(5065 characters)
:(
best,
Blessy Voola 4Blessy Voola 4
Hi Diaz

Try this for text5__c

right(Text__c,len(Text__c)-find(';',Text__c,find(text4__c,Text__c)))

Thanks
Please mark this answer as best if this resolves the issue.
This was selected as the best answer
Hector DiazHector Diaz
Hi Blessy
your formulas works excelent, thanks four your support :)
best,
CloudConsultan-CCloudConsultan-C
I'm trying to do the same but my delimiter is comma. When I get to text #3, I'm having trouble finding the best syntax to make it work. 
Could someone help write up a sample forumla for Text 3 with comma as delimiter? Thank you!