remove characters on both sides
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1

Answer: remove characters on both sides
 

Removing characters from both sides of a text string in Excel

Yes, you can remove characters from both sides of a text string at the same time using a combination of Excel functions. One way to do this is by using the MID function along with the LEN function.

Here's an example:

Let's say you have a text string in cell A1 that looks like this: "###Hello World###"

To remove the "#" characters from both sides of the text string, you can use the following formula:

1. = MID(A1, LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1, LEN(A1)-2*LEN(SUBSTITUTE(A1,"#","")))

Here's how this formula works:

1. The SUBSTITUTE function is used to count the number of "#" characters in the text string. This is done by replacing all "#" characters with an empty string ("") and then subtracting the length of the resulting string from the length of the original string using the LEN function. This gives us the total number of "#" characters in the string.

2. We then subtract twice the number of "#" characters from the length of the string to get the length of the text string without the "#" characters on both sides.

3. Finally, we use the MID function to extract the text string without the "#" characters. The starting position for the MID function is calculated by subtracting the length of the text string without the "#" characters from the length of the original string (minus one, since we want to start from the first character after the "#" characters).

When you enter this formula in a cell, it will return "Hello World" without the "#" characters on both sides.

remove characters on both sides
 

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.

--
Rick (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:919fad3644dc2@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1

remove characters on both sides
 

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

--
Message posted via http://www.officekb.com

remove characters on both sides
 

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.

--
Rick (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:91a0b4a9cf1ec@uwe...
can you also insert a text or any character from another text?
e.g.
insert letter "C" in the middle from the word "ABDE"

--
Message posted via http://www.officekb.com

remove characters on both sides
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1

remove characters on both sides
 

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

enrico via OfficeKB.com wrote:
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