Excel

November 19, 2008 11:53 AM

With VBA, how can I create a macro that puts the value of a cell in the middle of the text of another cell?

I want to have two columns (A and B) like this:

A B
hello ( ) 1
hello ( ) 4
hello ( ) 6
hello ( ) 3
hello ( ) 6
hello ( ) 9
hello ( ) 4

end up like this:

hello (1)
hello (4)
hello (6)
hello (3)
hello (6)
hello (9)
hello (4)

How can I make this happen?

posted by amsterdam63 to computers & internet (8 comments total)

pretty easy... are the parenthesis always in the same place, or variable locations? (i.e. is it ALWAYS hello( or is it

hello(
hi (

etc
posted by arniec at 12:14 PM on November 19, 2008

You don't need VBA. You can do this with some combination of the concatenate, left and right formulas. If the space is variable like arniec is asking about, you'll need the find formula, but that's it.
posted by Dec One at 12:17 PM on November 19, 2008

Yea, it would always be immediately after hello, but the number inside would sometimes be 3 digits.
posted by amsterdam63 at 12:19 PM on November 19, 2008

There's a zillion ways to do this, depending upon what is important in your ssimplified (I presume) example.

Here's one:

Sub thing()
For Each c In Range("A1:A7")
c.Value = Left(c.Value, 6) & c.Offset(0, 1).Value & Right(c.Value, 1)
Next c
End Sub
posted by pompomtom at 12:21 PM on November 19, 2008

Forget the question, here's what you need.

Sub MergeCols()

Dim i As Integer
Dim numOfRows As Integer
Dim sourceCol As String
Dim targetCol As String
Dim openParen As Integer
Dim tempstring As String

numOfRows = 10 'change this to the number of rows you have
targetCol = "A" 'change this to the letter of the column that has "hello ()"
sourceCol = "B" 'change this to the letter of the column that has the number

For i = 1 To numOfRows
tempstring = Range(targetCol & i)
openParen = InStr(tempstring, "(")
Range(targetCol & i) = Left(tempstring, openParen) & _
Range(sourceCol & i) & Right(tempstring, Len(tempstring) - openParen)

Range(sourceCol & i) = ""
Next i
End Sub
posted by arniec at 12:21 PM on November 19, 2008

Sub hi()

MyVar = "123"
ActiveSheet.Range("A1").Value = "hello(" & MyVar

End Sub

results in hello(123
which is close, but I can't figure out how to get the last ) tacked on there.
posted by amsterdam63 at 12:22 PM on November 19, 2008

Try this:

Sub hi()

MyVar = "123"
ActiveSheet.Range("A1").Value = "hello(" & MyVar & ")"

End Sub
posted by bzn at 12:29 PM on November 19, 2008

Here's the formula I came up with:

=LEFT(A1,(FIND("()",A1,1)))&B1&")"

Where column A has your "hello()" and column B has your number.

If your hello text parentheses have a space between them, add it into the FIND part:

=LEFT(A1,(FIND("( )",A1,1)))&B1&")"
posted by odinsdream at 12:32 PM on November 19, 2008