Deleting first & last character of a field

B

Bogga

I have an Excel spreadsheet that has a field for teacher names. My problem is that the names show up as |Smith, John|Doe, Mary| for example. (The class has 2 teachers). I can't use the find/replace because I don't want to get rid of the | that separates the 2 teachers names. I just want to get rid of the ones that are in the beginning of the field and the end of the field. Any ideas on how to do that?
 
C

Chip Pearson

Bogga,

In an emty column, use a formula like the following:

=MID(A1,2,LEN(A1)-2)

Copy thig formual down as far as you need to go. Then, copy
these values and do a Paste Special, Values, over the original
data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Bogga said:
I have an Excel spreadsheet that has a field for teacher names.
Click to expand...
My problem is that the names show up as |Smith, John|Doe, Mary|
for example. (The class has 2 teachers). I can't use the
find/replace because I don't want to get rid of the | that
separates the 2 teachers names. I just want to get rid of the
ones that are in the beginning of the field and the end of the
field. Any ideas on how to do that?
 
B

Bob Phillips

On the off-chance that the string may or may not have leading or trailing
zeroes, this variation should work

=MID(A1,IF(MID(A1,1,1)="|",2,1),LEN(A1)-IF(MID(A1,1,1)="|",1,0)-IF(MID(A1,LE
N(A1),1)="|",1,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bogga said:
I have an Excel spreadsheet that has a field for teacher names. My
Click to expand...
problem is that the names show up as |Smith, John|Doe, Mary| for example.
(The class has 2 teachers). I can't use the find/replace because I don't
want to get rid of the | that separates the 2 teachers names. I just want
to get rid of the ones that are in the beginning of the field and the end of
the field. Any ideas on how to do that?
 
X

xld

You asked this question yesteday under the handle Bogga.

This is what was given

On the off-chance that the string may or may not have leading o
trailing
zeroes, this variation should work

=MID(A1,IF(MID(A1,1,1)="|",2,1),LEN(A1)-IF(MID(A1,1,1)="|",1,0)-IF(MID(A1,LE
N(A1),1)="|",1,0))

Chip Pearson gave the simpler

In an emty column, use a formula like the following:

=MID(A1,2,LEN(A1)-2)

Copy thig formual down as far as you need to go. Then, copy
these values and do a Paste Special, Values, over the original
data
 
Top