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 (7 answers 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

Response by poster: 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

Response by poster: 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