Formula: inserting multiple dots in a string of text

[GigiChoi]

Hello, I have a text example ABCDE12345XY and I would like to insert periods "." after certain number of characters and express it as ABCDE.12345.XY. 

I tried the Left and Mid functions, but as soon as I start with the MID function, I get an error message. 

Help would be appreciated. Thank you. 

[edwin]

are you doing it in script or UI?

[QFabian]

Hi @GigiChoi , please try this example, it works with fixed lenght of texts, so if you want to have diynamic separator or dot positions, let me know to think somerthing else :

A:
load * inline [
F1
ABCDE12345XY
];

B:
Load
F1,
left(F1, 5) & '.' & mid(F1, 6, 5) & '.' & right(F1, 2) as F2
Resident A;

drop table A;

this is the result :

[Ksrinivasan]

hi,

if G2 is your field=ABCDE12345XY

=CONCAT(LEFT(G2,5),'.',RIGHT(LEFT(G2,10),5),'.',RIGHT(G2,2))

result

ABCDE.12345.XY.

ksrinivasan

[NITROG3NN]

Hi @GigiChoi ,

If you want to do it in UI you can use the following expression,

=MID(Number,1,5)&'.'&MID(Number,6,5)&'.'&MID(Number,11,2) 

or

=Left(Number,5)&'.'&MID(Number,6,5)&'.'&Right(Number,2)

basically gives the same output.

[GigiChoi]

QFabian, 

Thank you! I'm looking for a solution that would solve texts that are not fixed. 

So, after "ABCDE.12345.XY." I would like to have the capability that any other strings that are longer would have dots in every two letters that follow. 

Would you be able to help?

[edwin]

this would be better done in script not UI.  you want to first get the max len of string and get the ceil(MAXLEN/5) to get the max number of 5-char groups.

[edwin]

try this:

data:
load *,ceil(len(str)/5) as NumOfSections, 1 as Loop, left(str,5) as ComposedStr inline [
str
asdasfkhjksdghfuiw123
adasfasdf
rtergdfdfdfbdf
];

max:load max(ceil(len(str)/5)) as maxLen Resident data;

let vMax=peek('maxLen',0,max);

for i=2 to $(vMax)
	Concatenate (data) load  str, NumOfSections,
		if(Loop<NumOfSections, ComposedStr& '...' & mid(str,($(i)-1)*5+1,5), ComposedStr) as ComposedStr,
		$(i) as Loop
	Resident
	data
	where Loop=$(i)-1;
next

// inner join (data) load $(vMax) as Loop;

i commented the last statement so you see whats happening.  uncomment it to get the final result

[edwin]

what this does is first determine a few necessary info: how may groups of 5 per str, whats the first group of 5, max number of grouping

then it loops through the groupings.  gets the 5 char chunk and accumulates it adding dots as it goes along, and it uses the last accumulated string.  in the end you will get all the stages from loop 1 to final loop, inner join to filter out prior steps as you only need the final

[edwin]

oh yes, i did ... so its visible, you can always adjust