My demand is:

the seats number are like:

1A

2A 2D 2E

3A 3D 3E

4A 4D 4E

5A 5D 5E

If input 2ADE or 2A 2D 2E, then convert into 2A2D2E instead

If input 2A 3DE 4ADE, then convert into 2A 3D3E 4A4D4E instead

Any suggestion? Thanks!

My demand is:

the seats number are like:

1A

2A 2D 2E

3A 3D 3E

4A 4D 4E

5A 5D 5E

If input 2ADE or 2A 2D 2E, then convert into 2A2D2E instead

If input 2A 3DE 4ADE, then convert into 2A 3D3E 4A4D4E instead

Any suggestion? Thanks!

- 2 people found this helpful
Create a custom function:

**SeatString ( _list )**=*Let ( [**_input = GetValue ( _list ; 1 )**; _list = RightValues ( _list ; ValueCount ( _list ) - 1 )**; _row = Left ( _input ; 1 )**; _nextrow = Left ( GetValue ( _list ; 1 ) ; 1 )**; _seats = Filter ( _input ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )**; _string = Substitute ( _seats**; ["A";_row & "A"]**; ["B";_row & "B"]**; ["C";_row & "C"]**; ["D";_row & "D"]**; ["E";_row & "E"]**; ["F";_row & "F"]**; ["G";_row & "G"]**; ["H";_row & "H"]**; ["I";_row & "I"]**; ["J";_row & "J"]**; ["K";_row & "K"]**; ["L";_row & "L"]**; ["M";_row & "M"]**; ["N";_row & "N"]**; ["O";_row & "O"]**; ["P";_row & "P"]**; ["Q";_row & "Q"]**; ["R";_row & "R"]**; ["S";_row & "S"]**; ["T";_row & "T"]**; ["U";_row & "U"]**; ["V";_row & "V"]**; ["W";_row & "W"]**; ["X";_row & "X"]**; ["Y";_row & "Y"]**; ["Z";_row & "Z"]**)**] ;**_string & If ( ValueCount ( _list ) ; If ( _nextrow ≠ _row ; " " ) & SeatString ( _list ) )**)*and then call it like this:

*Let ( [**_string = "2A 2D 2E 3DE 4ADE"**] ;**SeatString ( Substitute ( _string ; " " ; "¶" ) )**)*Result = 2A2D2E 3D3E 4A4D4E

best

Otmar

Oh I see;-) You could move the Upper()-to the CF, simpler formula in the auto-enter calc.

Let ( [

_input = Upper ( GetValue ( _list ; 1 ) )

; _list = RightValues ( _list ; ValueCount ( _list ) - 1 )

; _row = Left ( _input ; 1 )

.....