TrailingSpaces

  • Thread starter Thread starter jb
  • Start date Start date
J

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
 
Hi
works for me. the entry 'John H.' will result in 'H.' without any
blanks between 'H' and '.'??
Frank
 
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))) & ".")
Click to expand...

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Try
=IF(ISERROR(FIND("
",Trim(C124),1)),"",RIGHT(Trim(C124),LEN(Trim(C124)) -FIND("
",Trim(C124),1))& ".")
 
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]
 
Back
Top