Combining One cell to Middle of text of another (example)

**decorjoe** · 06-13-2012, 12:58 PM

Hello

I would like to combine 2 cells, but I would like it to be in middle of text in the other cell

EXAMPLE:

Cell A1 = 123
Cell B1 = ABCDE

I would like Cell C1 to read ABC123DE

Thank You
Joe

**Russell Dawson** · 06-13-2012, 01:10 PM

=mid(b1,1,3)&a1&right(b1,2)

**decorjoe** · 06-13-2012, 02:23 PM

thanks for the quick reply. Now i'm curious, what if the range changes through the document

ie.
B1 = ABCDE
B2 = ABCDEFG

The truth is, it seems consistent that all rows in column A will always be going 2 characters from the right.

ie.
AB123CD
ABCDE123FG

is there a way to do this formula with just using the "Right" function?

**Russell Dawson** · 06-13-2012, 02:39 PM

You could use =LEFT(B2,3)&A2&MID(B2,4,5)

The right function would not do it as it counts back from the right. Having MID 4,5 selects the fourth character and up to 5 characters to it's right without getting an error if it is only three characters as per your first example.

Personally I'd go with =MID(B3,1,3)&A3&MID(B3,4,5)