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?
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?
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
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
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
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 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
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
Sub hi()
MyVar = "123"
ActiveSheet.Range("A1").Value = "hello(" & MyVar & ")"
End Sub
posted by bzn at 12:29 PM on November 19, 2008
This thread is closed to new comments.
hello(
hi (
etc
posted by arniec at 12:14 PM on November 19, 2008