Trying to find a neater way to do something.

 

I've a column which I want to pull the first 4 character from and then add to a string in another cell with a divider.

 

I've done it using   = LEFT(B5, 4) & "/" &

 

And repeating that for each cell in the column. It's just a bit clunky and I wondered if there was a clever way to do that for a cell range.

In terms of the formula that's fine and works for the one cell. I got the same result with the TRIM function: =LEFT(TRIM(A15),4)&"/"

 

To do a cell range I used the following formula looking at the data from column A1 to A10:

=LEFT(A1:A10,4)&"/"

 

 Do you have an example you could upload for us to help further?

Edited 29 March, 201610 yr by Robshaw

I found a bit of VBA that does everything other than just taking the left 4 characters. -

Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

If I use  - =ConcatenateRange(B5:B17, CHAR(5))

 

it puts a pipe between the cells which is neat but I could do with cutting it down to the first 4 characters still.

 

VBA is a bit beyond me, I don't play with it very often.

But the original =LEFT(B5,4)&"/" does exactly what you want it to do, I wouldn't over complicate the situation by using VBA.

 

What else do you want it to do?

 

 

Trying to find a neater way to do something.

 

I've a column which I want to pull the first 4 character from and then add to a string in another cell with a divider.

 

I've done it using   = LEFT(B5, 4) & "/" &

 

And repeating that for each cell in the column. It's just a bit clunky and I wondered if there was a clever way to do that for a cell range.

Nothing wrong with what you already have.

 

I assume you know about how to fill a formula down the column?

 

Just wanted a bit tidies so if I have to add any variables into the column they'd get included automatically into the range.

 

Plus I'm just being fussy and trying to have something a bit neater than that orrible clunky formula

In general, I use string slicing and ampersand("&") concatenation for this. I've never tried doing, say, {= LEFT(B5, 4) & "/" & $C$1/C5} though.

that bit of VBA works quite well but does anyone know how to edit it so it only selects the first 4 characters?

It's overcomplicating it to do a simple concatenation, but this only selects the first 4 characters

 

 

Function ConcatenateRange(ByVal cell_range As Range, _

                    Optional ByVal seperator As String) As String

 

Dim cell As Range

Dim newString As String

Dim cellArray As Variant

Dim i As Long, j As Long

 

cellArray = cell_range.Value

 

For i = 1 To UBound(cellArray, 1)

    For j = 1 To UBound(cellArray, 2)

        If Len(cellArray(i, j)) <> 0 Then

            newString = newString & (seperator & Left(cellArray(i, j), 4))

        End If

    Next

Next

 

If Len(newString) <> 0 Then

    newString = Right$(newString, (Len(newString) - Len(seperator)))

End If

 

ConcatenateRange = newString

 

End Function

 

Don't think it's that easy with the VBA as you can't embed the LEFT function in the formula.

 

So you'd have to embed in the VBA.

 

 

As softscoop has done above, and beat me to it!