I use the following formula to copy the middle initial and add a . after
it. The column being copied from has the firstname and middle initial
in the cells(John H). This has been working in thepast, but now it puts
spaces between the midddle initial and . when it is copied( John
..). How can I trim the trailing from the middle initial before adding
the period.

=IF(ISERROR(FIND(" ",C124,1)),"",RIGHT(C124,LEN(C124) -FIND("
",C124,1))& ".")

Thanks

Hi
works for me. the entry 'John H.' will result in 'H.' without any
blanks between 'H' and '.'??
Frank

jb wrote:
I use the following formula to copy the middle initial and add a .
after it. The column being copied from has the firstname and middle
initial in the cells(John H). This has been working in thepast, but
now it puts spaces between the midddle initial and . when it is
copied( John .). How can I trim the trailing from the middle initial
before adding the period.

=IF(ISERROR(FIND(" ",C124,1)),"",RIGHT(C124,LEN(C124) -FIND("
",C124,1))& ".")

Thanks

Hi jb,

I'm guessing the values in your name column have one or more trailing spaces
in them. You can use the TRIM function in your formula to remedy this:

=IF(ISERROR(FIND(" ",C124,1)),"",TRIM(RIGHT(C124,LEN(C124) -FIND("
",C124,1))) & ".")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


jb wrote:
I use the following formula to copy the middle initial and add a .
after it. The column being copied from has the firstname and middle
initial in the cells(John H). This has been working in thepast, but
now it puts spaces between the midddle initial and . when it is
copied( John .). How can I trim the trailing from the middle initial
before adding the period.

=IF(ISERROR(FIND(" ",C124,1)),"",RIGHT(C124,LEN(C124) -FIND("
",C124,1))& ".")

Thanks

On second thought, you may want to use TRIM on all references to the cell:

=IF(ISERROR(FIND("
",TRIM(C124),1)),"",RIGHT(TRIM(C124),LEN(TRIM(C124 )) -FIND("
",TRIM(C124),1)) & ".")

This will help in cases where you have trailing spaces on a first name with
no middle initial.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Jake Marx wrote:
Hi jb,

I'm guessing the values in your name column have one or more trailing
spaces in them. You can use the TRIM function in your formula to
remedy this:

=IF(ISERROR(FIND(" ",C124,1)),"",TRIM(RIGHT(C124,LEN(C124) -FIND("
",C124,1))) & ".")


jb wrote:
I use the following formula to copy the middle initial and add a .
after it. The column being copied from has the firstname and middle
initial in the cells(John H). This has been working in thepast, but
now it puts spaces between the midddle initial and . when it is
copied( John .). How can I trim the trailing from the middle initial
before adding the period.

=IF(ISERROR(FIND(" ",C124,1)),"",RIGHT(C124,LEN(C124) -FIND("
",C124,1))& ".")

Thanks

Try
=IF(ISERROR(FIND("
",Trim(C124),1)),"",RIGHT(Trim(C124),LEN(Trim(C124 )) -FIND("
",Trim(C124),1))& ".")

--
Regards,
Tom Ogilvy


"jb" wrote in message
...
I use the following formula to copy the middle initial and add a . after
it. The column being copied from has the firstname and middle initial
in the cells(John H). This has been working in thepast, but now it puts
spaces between the midddle initial and . when it is copied( John
.). How can I trim the trailing from the middle initial before adding
the period.

=IF(ISERROR(FIND(" ",C124,1)),"",RIGHT(C124,LEN(C124) -FIND("
",C124,1))& ".")

Thanks