How do I automate bracketing of text in an Excel cell?
 

I have a column of text that all begin and end with a bracket ([]). The text
varies, but the start and finish should all show brackets. How do I automate
the entry of this after all the text is entered. I tried to create a macro by
typing the first bracket, then entering the "end" key and typing the end
bracket. That routine copied all the text between the brackets, as well as
the brackets. Any ideas?

How do I automate bracketing of text in an Excel cell?
 

If you are looking for a macro to convert the entries to this format then try
the below macro.

Sub Macro1()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("A" & lngRow) < "" And Left(Range("A" & lngRow), 1) < "[" Then
Range("A" & lngRow) = "[" & Range("a" & lngRow).Text & "]"
End If
Next
End Sub

'If you are looking to automate this as soon as you type try the below
worksheet event. Select the sheet tab which you want to work with. Right
click the sheet tab and click on 'View Code'. This will launch VBE. Paste the
below code to the right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
If Target.Text < "" And Left(Target.Text, 1) < "[" Then
Application.EnableEvents = False
Target = "[" & Target.Text & "]"
Application.EnableEvents = True
End If
End If
End Sub



--
Jacob


"Kevin" wrote:

I have a column of text that all begin and end with a bracket ([]). The text
varies, but the start and finish should all show brackets. How do I automate
the entry of this after all the text is entered. I tried to create a macro by
typing the first bracket, then entering the "end" key and typing the end
bracket. That routine copied all the text between the brackets, as well as
the brackets. Any ideas?

How do I automate bracketing of text in an Excel cell?
 

Hi Kevin,

I have a column of text that all begin and end with a bracket ([]). The text
varies, but the start and finish should all show brackets. How do I automate
the entry of this after all the text is entered. I tried to create a macro by
typing the first bracket, then entering the "end" key and typing the end
bracket.

A formula will do:

="[" & A2 & "]"

Copy down to match your # of rows.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com