Combine 2 text cells into 1 1

[scrappe7]

Howdy all,

I'd like to reference a cell on sheet1 to be equal to cell A1 and A2. The value of those cells contain last names. I want my cell on sheet 1 to display the references as Smith, Jones. How do I reference a cell to display multiple text values, thanks.

Ed

 

[danhauer]

You could write a formula in your cell on sheet one to say:
=A1 & ", " & B1

so, if A1 = "Smith" and B1 = "Jones", the formula's results would be Smith, Jones

Is that what you wanted?

Danhauer

 

[scrappe7]

That works but I would actually like the 2nd cell to appear below the 1st one. If I use ALT + Enter i lose the formula and what gets displayed is the real formula entered [=A1 & "," & A2] etc. The formula stops calculating. What to do?

Should look like: Smith
Jones

Anyway around this....

Thanks
Ed

 

[danhauer]

=A1 & char(10) & A2 will give you the contents in A1, then a carriage return (like alt-enter) then the contents in A2.
(Of course you have to format the cell to wrap text.)

That do the trick?

Danhauer

 

[scrappe7]

worked like a charm, thanks a mil. I have another question you might be able to help with. Can i format a cell that will have a first and last name entered in it to always display as J. Smith. So even if you entered John Smith, Jon Smith, Johnatan Smith, Jo Smith it would display always as J. Smith. I need the First Initial period, Last to conform to an access table, thanks.

 

[vbMax]

Try this...
If the name is in cell A1, enter the following formula in cell B1:

=MID(A1,1,1) & ". " & MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

 

[scrappe7]

vbMax i see what ur saying but i cant use an extra row for formatting. Ive received some tips to use vb and it looks like this so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.Range("i:n&quot) _
Is Nothing Then
Dim strCell As String
Dim strRet As String
Dim intMid As Integer
strCell = Target.Value
If Not IsNumeric(strCell) And strCell <> &quot;&quot; Then
Application.EnableEvents = False
intMid = InStr(1, strCell, &quot; &quot;, 1)
strRet = Left(strCell, 1) & &quot;. &quot; & _
Right(strCell, Len(strCell) - intMid)
Target.Value = strRet
Application.EnableEvents = True
End If
End If
End Sub

The only thing i need to edit is to make the first letter of the middle name also appear if one is enter. Do you know how to edit this , thanks a lot.