Re: Calling Bob Phillips - Please

Hi Mike,

The cell can hold test strings of up to 32k characters.

Regards,
Peter

"Mike Fogleman" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:#v7os74PGHA.1088@xxxxxxxxxxxxxxxxxxxxxxx
The limit is Excel's number accuracy is 15 digits. See Tools/Options -
Calculation, Precision as Displayed. Use the ? balloon help. The 16th
digit
and up are truncated to 0 in the cell display. To get around this, format
the cell as a custom text number, which allows up to 256 characters in the
cell display. The format symbol is @. This will get you past your current
number limit of 15, but the limit for all 70 columns still remains at 3
characters each. 70*3=210 concatenated characters.
Bob's revision effectively gets you there, but your new limit is 256
concatenated characters.

Mike F

"Michael Beckinsale" <michael.beckinsale@xxxxxxxxxxxx> wrote in message
news:e1JuJ23PGHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
Bob,

On the 13th Janaury you were kind enough to provide me with a function
that concatenated values across approx 70 columns for a dynamic number
of
rows.

That code is posted below.

Since then l have been testing the workbook, before releasing it, and
have
now found a problem. The user can enter a 3 digit code in any of the 70
columns up to a maximum of 15. Therefore the maximum number of digits
that
can be concatenated is 45. However when that number of digits is
concatenated the result ends up as a number raised to a power.(The
function works fine when only 4 or 5 sets of 3 digits are entered)

ie these digits when concatenated

100
101
200
201
203
219
225
300
301
400
401
500
550
600
900

end up being displayed as

100101200201203000000000000000000000000000000

and the value displayed in the formula bar is

1.00101200201203E+44

what l really need to see is a string as follows

100101200201203219225300301400401500550600

because in the next 15 columns l extract each of the 3 digit codes using
functions (for example Left(A1,3) Mid(A1,4,3) etc)

Any ideas as to whats going wrong and what the solution is please?

Alternatively is it possible to 'loop thru' each of the 70 columns and
when the 1st 3 digit value is found put it into say column 100, the 2nd
3
digit value in column 101 etc for each row in the dynamic range?

xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx

This works from row 1 to the last row and concatenates values from
column
2
out, and drop the result in column A. Change to suit

Sub ConcatenateRange()
Dim val As String
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long
Dim j As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
val = ""
For j = 2 To iLastCol
val = val & Cells(i, j)
Next j
Cells(i, "A").Value = val
Next i
End Sub

Kind regards

Michael Beckinsale






.

Relevant Pages

  • Re: Calling Bob Phillips - Please
    ... The limit is Excel's number accuracy is 15 digits. ... and up are truncated to 0 in the cell display. ... This works from row 1 to the last row and concatenates values from column ... Dim iLastRow As Long ...
    (microsoft.public.excel.programming)
  • Re: color format
    ... system each byte is displayed as two digits, ... to display them. ... with the highest order byte containing zero. ... The use of type-declaration characters is frowned upon ...
    (microsoft.public.vb.general.discussion)
  • Re: Alternative to AVR Butterfly?
    ... display, like an AVR Butterfly except that the LCD is easy to use? ... Butterfly's LCD is segment-addressable and if you want digits or characters, ...
    (comp.arch.embedded)
  • Re: Mapping Characters to Integers
    ... I however got to see the last digits of the message_int and they were ... incorrect. ... Do you think I should take out the display and see if it correctly comes out? ... would be generated would only be the first 25000 characters of the output, ...
    (comp.soft-sys.matlab)
  • Re: Alternative to AVR Butterfly?
    ... display, like an AVR Butterfly except that the LCD is easy to use? ... Butterfly's LCD is segment-addressable and if you want digits or characters, ...
    (comp.arch.embedded)
Loading