Position Numbers

**Dubrock** · 11-01-2013, 06:00 AM

In the German Gaeb-files. d83, d84 etc there is a type of coding for the position numbers. In the line with code 00 there is a code like "123PPI000" which is used like a kind of translation code for all position numbers. Here a couple op positions and what they should look like in the print:

1 09 --> 1.09
21 --> 2.1
21 02 --> 2.1.02

Don't know if you guys are familiar which such translations, but '123PPI000' means basically that theirs one 1, then a dot; one 2 then a dot; one 3 then a dot, two P's, then a dot etc. in case all positions are used.
In the example above with 1 09 the 2 and 3 are not used, with 21 only the 1 and 2 are used.
I can split the positions code in different fields and then add them with concatenate add them together and add the dots. problem is when parts are not used it should NOT result in 1...09. To avoid that many ifs are needed.

Whats the easiest way to get from 1 09 --> 1.09 with the 123PPI000 code? This 'translation code' is different for every tender, but is the same within the tender for all positions.

Maybe convert to 1. . .09 and then delete spaces and the the double dots, but what formula to use for that?

**XOR LX** · 11-01-2013, 06:23 AM

Hi,

Could you post a workbook with some examples and your desired result in each case?

Regards

**ragulduy** · 11-01-2013, 06:25 AM

I think maybe if you had morefunc's mconcat or something similar to concatenate an array you could use something like:
=MCONCAT(MID(LEFT(A1,FIND(" ",A1)-1),ROW(INDIRECT("A1:A"&LEN(LEFT(A1,FIND(" ",A1))-1))),1)&".")&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1))

Or you could write your own user-defined-function to do it.

**Dubrock** · 11-01-2013, 06:43 AM

Here the attachment with all the positions in one specific tender. Be aware that the formula is different now in many cases. I am looking for a fomula that can be used for all situations, and can maybe even skip the step of splitting up the original position without the dots. Or would that be to much at once?