TrailingSpaces

[jb]

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

 

[Frank Kabel]

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

 

[Jake Marx]

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]

 

[Tom Ogilvy]

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

 

[Jake Marx]

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]