I see from your comment that your cells most likely have custom formats, and the displayed value in these cells is likely different from the entered value (e.g. "8" is shown as "08"). In this case, you can concatenate each value wrapped in a
TEXT function with the specified format of the cell. For example, if
A1 has custom format "00", you would use
TEXT(A1,"00") as the term for
A1 in your concatenation formula. For the full formula, you may have something like this:
Of course, this is tedious as it requires you to manually recreate the format of each cell. If you're going to be using this a lot with lots of varied formats, I would say this is a perfect opportunity to use a VBA function to do the heavy lifting. You can paste the following code into a module in the VBA Editor (opened by pressing Alt+F11).
Public Function CONCATwFORMATS(rng1 As Range) As String
Dim tmpstr As String, tmpFormat As String
Dim c As Range
For Each c In rng1
tmpFormat = c.NumberFormat
If tmpFormat = "General" Then
tmpstr = tmpstr & c.Value
tmpstr = tmpstr & Format(c.Value, tmpFormat)
CONCATwFORMATS = tmpstr
Then, you can use the following formula in
This is just a quick attempt at this code, so note two requirements of the function:
- It only takes a contiguous range as an argument.
- It concatenates the values in order from left to right (and top to bottom).
The code can be tweaked to remove these restrictions, but for what you want, it sounds like this will work fine.