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
=============
