Text Functions

W

wilson o

Can I have text from cells in one coloumn automatically added to the
beginning of anything that is typed in the same row of another coloumn?
 
G

Gord Dibben

You could use a formula and helper column

A1 contains "this is"

In C1 enter this formula =A1 & " " B1 Note: the " " adds a space.

In B1 type "combined text"

C1 will return "this is combined text"

To have it happen without a formula you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
If Target.Column = 2 Then
Application.EnableEvents = False
With Target
.Value = .Offset(0, -1).Value & " " & .Value
End With
End If
endit:
Application.EnableEvents = True
End Sub

When you type text in column B then hit enter, that text will be prefaced by the
text in Column A

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Alt + q to go back to Excel window.


Gord Dibben MS Excel MVP
 
W

wilson o

Thank you.

Gord Dibben said:
You could use a formula and helper column

A1 contains "this is"

In C1 enter this formula =A1 & " " B1 Note: the " " adds a space.

In B1 type "combined text"

C1 will return "this is combined text"

To have it happen without a formula you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
If Target.Column = 2 Then
Application.EnableEvents = False
With Target
.Value = .Offset(0, -1).Value & " " & .Value
End With
End If
endit:
Application.EnableEvents = True
End Sub

When you type text in column B then hit enter, that text will be prefaced by the
text in Column A

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Alt + q to go back to Excel window.


Gord Dibben MS Excel MVP
Click to expand...
 
Top