CODEWITHSUNDEEP
excelexcel-formula
Jessica Howard
Jessica Howard

Reputation: 25

If a cell contains two words, how would I use the first letter of the first word but use all the letters of the second word?

Cell A1 contains a name e.g. "Joe Public"

B1 needs to output "jpublic"

The best I can do is:

=LOWER(LEFT(SUBSTITUTE(A1," ",""),1)) which outputs "j"

This make everything lower case takes the first character from the left and removes the spaces between the name, but how do I get the whole of the surname?

EDIT

I have achieved my goal, but can it be tidied up at all?

=LOWER(CONCATENATE(LEFT(A2,1),IF(ISERROR(FIND(" ",A2,start-pos)),"",RIGHT(A2,LEN(A2)−FIND("",SUBSTITUTE(A2," ","",LEN(A2)−LEN(SUBSTITUTE(A2," ","",occurrence))),start-pos)))))

Upvotes: 0

Views: 2301

Answers (3)

user4039065
user4039065

Reputation:

This one works for names with a single or no space,

=LOWER(REPLACE(A1,2,IFERROR(FIND(" ",A1)-1,0),""))

To guard against more than a single space in the full name (e.g. Hans Christian Anderson) we could SUBSTITUTE() out any additional spaces.

=SUBSTITUTE(LOWER(REPLACE(A3,2,IFERROR(FIND(" ",A3)-1,0),"")), " ", "")

Upvotes: 0

Donal
Donal

Reputation: 32713

You need to use SEARCH - to find the index of the space. Take the RIGHT of that and CONCATENATE with the first character.

=LOWER(CONCATENATE(LEFT(A1, 1), RIGHT(A1, LEN(A1) - SEARCH(" ",A1))))

enter image description here

Upvotes: 2

CaptBallistic
CaptBallistic

Reputation: 38

You could use this, but it supposes a space between the forename and the surname:

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

Upvotes: 0

Related Questions