Sorted Fixed Length String

[Paul Black]

Hi Everyone,

I have a String of Numerical Digits Created Using Concatenate.
The Strings could be from 6 Characters in Length to 11 Characters in
Length.
The Most Characters in a String with a Digit GREATER than 1 can ONLY be
6, the Remainder of the Digits will be 0.
The Least Characters in a String with a Digit GREATER than 1 can ONLY be
1, the Remainder of the Digits will be 0.

Is there a Formula that will Produce a String of ONLY 6 Characters Long
Please.
Is it also Possible to Sort the String with the Highest Digit on the
Left to the Lowest Digit on the Right Please.

For Example :-
11111100000 would Become 111111
111120000 would Become 211110
11103000 would Become 311100
11220000 would Become 221100
00510000000 would Become 510000
123000 would Become 321000

Thanks in Advance.
All the Best.
Paul

 

[Bob Phillips]

Hi Paul,

My solution requires a mix if a UDF and worksheet formula.

This array formula

=LARGE(--(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A6")))

can work out the six numbers and drop them into an array, but we cannot
concatenate the individual items as array formulas do not support
concatenation.

Thus with this UDF

Function ConcatArray(ByVal Text As Variant) As String
Dim i As Long
For i = 1 To UBound(Text)
ConcatArray = ConcatArray & Text(i, 1)
Next
End Function

which added to the array formula means that we use

=CONCATARRAY(LARGE(--(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT(
"A1:A6"))))

which is still an array formula.

 

[Paul Black]

Thanks for the Reply Bob,

I have Changed the Concatenate Formulas so they Now Produce the String
in the Following Format :-

00000111111
00030111
000021111
00002211
000321

In the Next Column I have Used the Formula ...

=RIGHT(A1,6)

... which Produces :-

00000111111 Becomes 111111
00030111 Becomes 030111
000021111 Becomes 021111
00002211 Becomes 002211
000321 Becomes 000321

Now what I would like is for it to Remove ALL the Zeros so ...

00000111111 Becomes 111111 Becomes 111111
00030111 Becomes 030111 Becomes 3111
000021111 Becomes 021111 Becomes 21111
00002211 Becomes 002211 Becomes 2211
000321 Becomes 000321 Becomes 321

... and then Put 0's at the End so it is Still a 6 Digit String, so ...

00000111111 Becomes 111111 Becomes 111111 Becomes 111111
00030111 Becomes 030111 Becomes 3111 Becomes 311100
000021111 Becomes 021111 Becomes 21111 Becomes 211110
00002211 Becomes 002211 Becomes 2211 Becomes 221100
000321 Becomes 000321 Becomes 321 Becomes 321000

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

I have come up with the Formula ...

=SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")

... which does what I want and gets me to ...

111111
3111
21111
2211
321

The Only thing I Need is to get it to Fill the Right Side of the Result
with Zeros Making it a 6 Digit String ...

111111 Becomes 111111
3111 Becomes 311100
21111 Becomes 211110
2211 Becomes 221100
321 Becomes 321000

Thanks in Advance.
All the Best.
Paul



Re: Sorted Fixed Length String
From: Paul Black

Thanks for the Reply Bob,

I have Changed the Concatenate Formulas so they Now Produce the String
in the Following Format :-

00000111111
00030111
000021111
00002211
000321

In the Next Column I have Used the Formula ...

=RIGHT(A1,6)

... which Produces :-

00000111111 Becomes 111111
00030111 Becomes 030111
000021111 Becomes 021111
00002211 Becomes 002211
000321 Becomes 000321

Now what I would like is for it to Remove ALL the Zeros so ...

00000111111 Becomes 111111 Becomes 111111
00030111 Becomes 030111 Becomes 3111
000021111 Becomes 021111 Becomes 21111
00002211 Becomes 002211 Becomes 2211
000321 Becomes 000321 Becomes 321

... and then Put 0's at the End so it is Still a 6 Digit String, so ...

00000111111 Becomes 111111 Becomes 111111 Becomes 111111
00030111 Becomes 030111 Becomes 3111 Becomes 311100
000021111 Becomes 021111 Becomes 21111 Becomes 211110
00002211 Becomes 002211 Becomes 2211 Becomes 221100
000321 Becomes 000321 Becomes 321 Becomes 321000

Thanks in Advance.
All the Best.
Paul

 

[Ron Rosenfeld]

Hi Everyone,

I have a String of Numerical Digits Created Using Concatenate.
The Strings could be from 6 Characters in Length to 11 Characters in
Length.
The Most Characters in a String with a Digit GREATER than 1 can ONLY be
6, the Remainder of the Digits will be 0.
The Least Characters in a String with a Digit GREATER than 1 can ONLY be
1, the Remainder of the Digits will be 0.

Is there a Formula that will Produce a String of ONLY 6 Characters Long
Please.
Is it also Possible to Sort the String with the Highest Digit on the
Left to the Lowest Digit on the Right Please.

For Example :-
11111100000 would Become 111111
111120000 would Become 211110
11103000 would Become 311100
11220000 would Become 221100
00510000000 would Become 510000
123000 would Become 321000

Thanks in Advance.
All the Best.
Paul

Here is a UDF. I may have some unnecessary conversions in there but it seems
to work with multiple formats.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
UDF below into the window that opens.

To use this, enter =FixNum(cell_ref) into some cell and it will do the
conversion as you describe.

=====================================
Option Explicit

Function FixNum(num As Double) As Double
Dim str As String
Dim i As Long
Dim TempArray

str = Replace(CStr(num), "0", "", 1, -1, vbTextCompare)
ReDim TempArray(Len(str) - 1)
For i = 0 To UBound(TempArray)
TempArray(i) = Mid(str, i + 1, 1)
Next i
BubbleSort TempArray
str = StrReverse(Join(TempArray, ""))
str = Left(str & "000000", 6)

FixNum = CDbl(str)

End Function

Private Function BubbleSort(TempArray As Variant)
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 0 To UBound(TempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) > TempArray(i + 1) Then
NoExchanges = False
Temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = Temp
End If
Next i
Loop While Not (NoExchanges)

End Function

==================================


--ron

 

[Tom Ogilvy]

=Left(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&"000000
",6)

 

[Paul Black]

Thanks to Everyone for the Replies.

Tom,

Your Formula Works Great.
I Wanted to be Able to Find the Min, Max & Average of the Column, But
for Some Reason it Always Produced 0.
I Added ABS to the Formula which Allowed me to Achieve this, the
Finished Formula is Now :-

=ABS(LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")
& "000000", 6))

One Final Question Please.
I have Several Other Tasks where I Use the Concatenate Function which
Works Fine. The Only thing is, I cannot Use the Min, Max & Average on
the Columns. Is there a Specific Way of Building a Formula so that I am
Able to do this.
For Example, After Concatenation, the Results would be Something Like
this :-

03201
11202
11121
30021
13011
11220
23001
00132
00222

I Actually want to Find the Min, Max & Average Calculated on the Figures
as they are.

Thanks in Advance.
All the Best.
Paul



Re: Sorted Fixed Length String
From: Tom Ogilvy

=Left(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&"00
0000
",6)

 

[Bob Phillips]

I Wanted to be Able to Find the Min, Max & Average of the Column, But
for Some Reason it Always Produced 0.

This is because CONCATENATE and LEFT return strings, so you need to coerce
it into a number. ABS does that as you found, but any mathematical operation
would have worked, such as

=LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)+0
or
=LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)*1
or
=--LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)

One Final Question Please.
I have Several Other Tasks where I Use the Concatenate Function which
Works Fine. The Only thing is, I cannot Use the Min, Max & Average on
the Columns. Is there a Specific Way of Building a Formula so that I am
Able to do this.
For Example, After Concatenation, the Results would be Something Like
this :-

03201
11202
11121
30021
13011
11220
23001
00132
00222

I Actually want to Find the Min, Max & Average Calculated on the Figures
as they are.

Presumably, this is the same problem, strings not numbers.Again coerce to a
number, like so

=AVERAGE(--A1:A100)

which is an array formula, so commit with Ctrl-Shift-Enter.

 

[Paul Black]

Thanks Bob,

If in the Formula ( Min, Max Or Average ), I ONLY Use the Range with
Data in, it Works Perfectly. But if the Data Finishes in Cell A800 for
Example, and the Formula is Looking at the Range A1000 for Example, the
Minimum Returns a Value of 0, and the Average Returns the Incorrect
Figure. Is there Any Way to get the Minimum Value Greater than 0 and the
Average to Only Average on the Cells Greater than 0.

Thanks in Advance.
All the Best.
Paul



Re: Sorted Fixed Length String
From: Bob Phillips

I Wanted to be Able to Find the Min, Max & Average of the Column, But
for Some Reason it Always Produced 0.

This is because CONCATENATE and LEFT return strings, so you need to
coerce
it into a number. ABS does that as you found, but any mathematical
operation
would have worked, such as

=LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)+0
or
=LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)*1
or
=--LEFT(SUBSTITUTE(RIGHT(CONCATENATE(U14,V14,W14,X14,Y14,Z14),6),0,"")&
"000000", 6)

One Final Question Please.
I have Several Other Tasks where I Use the Concatenate Function which
Works Fine. The Only thing is, I cannot Use the Min, Max & Average on
the Columns. Is there a Specific Way of Building a Formula so that I am
Able to do this.
For Example, After Concatenation, the Results would be Something Like
this :-

03201
11202
11121
30021
13011
11220
23001
00132
00222

I Actually want to Find the Min, Max & Average Calculated on the Figures
as they are.

Presumably, this is the same problem, strings not numbers.Again coerce
to a
number, like so

=AVERAGE(--A1:A100)

which is an array formula, so commit with Ctrl-Shift-Enter.