MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Concatenate


Posted by Dan Peters on December 07, 2001 1:46 AM

How can i concatenate three cells if i only use the second and third characters from one of the cells? Eg. 12345 ABCDE abcde to create 12345BCa !


Posted by Colo on December 07, 2001 1:53 AM

Hi! Please try this formula.
[A1] 12345
[B1] ABCDE
[C1] abcde
[D1] =A1&MID(B1,2,2) & LEFT(C1,1)

Posted by Dan on December 07, 2001 3:54 AM

And how about if i wanted the 'e' on the end to be shown in ASCII coding? Is this possible?

Posted by Bariloche on December 07, 2001 5:10 AM

Dan,

The only way I could get it to evaluate Asc(Right(C1,1)) was by using VBA. Which I think is because the "Asc" function is not available in Excel but is available in VBA (at least on my machine).

Sub stringing()

MySubString = Cells(1, 1).Value & Mid(Cells(1, 2).Value, 2, 2) & Left(Cells(1, 3).Value, 1) & Asc(Right(Cells(1, 3).Value, 1))

Cells(1,4).Value = MySubString

End Sub

This returns: 12345BCa101

enjoy

Posted by Aladin Akyurek on December 07, 2001 5:29 AM

=...&CODE("E") or

=...&CODE("e")

whichever you want.

Note. The arg of code can also be a computation as in

=CODE(UPPER(RIGHT(A1))) or

=CODE(LOWER(RIGHT(A1)))

Aladin

=============