Calling Bob Phillips - Please

[Michael Beckinsale]

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

 

[Bob Phillips]

Michael,

Try this

Sub ConcatenateRange()
Dim val As String
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long
Dim j As Long
Columns(1).NumberFormat = "@"
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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mike Fogleman]

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

 

[Peter T]

Hi Mike,

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

Regards,
Peter