left and right formula help

[plost33]

i have a list of names. such as: John Smith.

I need a formula that will return: "JSmith"


I know this is some form or left and right but i dont know how to incorporate the formula identifying a "space" and taking whats right of that to get the last name. hope someone can assist.. thanks

 

[Andrew Poulsom]

If the original text is in A1:

=LEFT(A1,1)&RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[Jonmo1]

If the name is ALWAYS "First Last" then try

=REPLACE(A1,2,FIND(" ",A1)-1,"")

 

[JackDanIce]

Try:

=TRIM(LEFT(A1,1)&RIGHT(A1,FIND(" ",A1)))

 

[plost33]

thanks. that is great.

 

[plost33]

jonmo, how do i fix this formula you gave me to have it put into the referenced cell:

i have this so far:

Range("S" & r).formula = "=REPLACE(B7,2,FIND(" & " & " & ",B7)-1,"")



the formula i want to put in the cell is:

=REPLACE(B7,2,FIND(" ",B7)-1,"")


thanks

 

[Jonmo1]

The same way we did it in your other post
http://www.mrexcel.com/forum/showthread.php?t=554161

You don't need to concatenate to put quotes in the formula.
Instead, you just double up the quotes..

If you want this formula in the cell
=REPLACE(B7,2,FIND(" ",B7)-1,"")

In VBA, you just double up the quotes

Range(...).Formula = "=REPLACE(B7,2,FIND("" "",B7)-1,"""")"

hope that helps.

 

[plost33]

works. thanks jon.