remove characters on both sides

[enrico via OfficeKB.com]

can you remove characters from text on both sides at the same time? because
all i know is you can remove either only from right or left one at a time

 

[Rick Rothstein]

Yes you can, using the MID function. For example, if A1 contains the text
AB12345CDE and you wanted to remove the first two character and the last
three characters, you would use this formula...

=MID(A1,3,LEN(A1)-5)

where the 3 is one more than the number of characters you want to remove
from the left and 5 is the total number of characters you want to remove.

 

[enrico via OfficeKB.com]

can you also insert a text or any character from another text?
e.g.
insert letter "C" in the middle from the word "ABDE"

 

[Rick Rothstein]

Yes, by piecing parts of text together...

=LEFT(A1,2)&"C"&MID(A1,3,99)

where the 99 just has to be a number larger than the number of characters
after the start position (that is what the 3 is). Using a large number is
more efficient than calculating the exact value (a LEN function call and
some math is eliminated by doing so). The "exact" method would be this...

=LEFT(A1,2)&"C"&MID(A1,3,LEN(A1)-2)

but as I said, it is more efficient to do it the first way.

 

[enrico via OfficeKB.com]

what if i want to insert a period on the second to the last part of a field
but the number of characters varies?
e.g. 1375 = 137.5
625 = 62.5
500 = 50.0

 

[David Biddulph]

=A2/10, and format with one decimal place, if you want a number,
or =TEXT(A2/10,"#.0") if you want text.