I am trying to implement strings tokenizer in Excel via UDF using C# and Excel-Dna. Personally I found the mIRC's function $gettok pretty useful due to the lack of string functions in Excel.
Examples
getTok("Aquel que no espera vencer ya esta vencido", 1, 32) -> "Aquel"
getTok("Aquel que no espera vencer ya esta vencido", -1, 32) -> "vencido"
getTok("Aquel que no espera vencer ya esta vencido", 2-4, 32) -> "que no espera"
getTok("Aquel que no espera vencer ya esta vencido", 0, 32) -> "Aquel que no espera vencer ya esta vencido"
Code
[ExcelFunction(Category="String Utilities", Name = "getTok")]
public static string getTok(string text, string token, int delimiter)
{
int from = 0;
int to = 0;
string tokenPatter = @"(\d+)(-)(\d+)?";
string[] tokens = text.Split(new char[] { (char)delimiter });
Regex tokenRegex = new Regex(tokenPatter, RegexOptions.IgnoreCase);
Match tokenMatch = tokenRegex.Match(token);
if (tokenMatch.Success)
{
StringBuilder sb = new StringBuilder();
from = short.Parse(tokenMatch.Groups[0].Value);
if (tokenMatch.Groups.Count == 1) to = from;
else if (tokenMatch.Groups.Count == 2) to = (short)tokens.Length;
else to = short.Parse(tokenMatch.Groups[2].Value);
for (int i = from; i <= to; i++)
{
sb.Append(tokens[i - 1] + ((char)delimiter));
}
return sb.ToString();
}
int index = int.Parse(token);
if (index > 0) return tokens[index - 1];
else return tokens[tokens.Length + index];
}
Questions
- What would you improve from above code?
- Have you tried something similar with Excel?
geTok
begetTok
? – Mat's Mug Sep 5 '13 at 23:49