Can you write an IF statement based on a character in a string?-CodePudding

In excel I have a column that contains given names. Some of those are one word and some are two words. Think of something like this:

FIRST NAME
Emma
Anthony
Anne Marie
John

I want to concatenate this column with another one to create an id, and I am just interested in getting the first word. So my ideal output would look like column 'ID'

FIRST NAME | CODE | ID
Emma       | 2D1  | 2D1_Emma
Anthony    | 4G3  | 4G3_Anthony
Anne Marie | 8Y2  | 8Y2_Anne
John       | 5L9  | 5L9_John

I have tried it with this formula, but it is not working, it retrieves all the text in the first column instead of just the first word:

=CONCAT($B2;"_";IF($A2="* *";(LEFT($A2(FIND(" ";A2;1)-1)));A2))

If I don't use the * as a wildcard, the result I get is the same. Any other combinations I tried give an error.

Any idea how I can get it to pick the text on the left of a blank space if there is any? Thanks!

Brisa

CodePudding user response：

Concatenate a space when using FIND:

=B2&"_"&LEFT(A2,FIND(" ",A2&" ")-1)

Since your version of Excel uses ; as the separator:

=B2&"_"&LEFT(A2;FIND(" ";A2&" ")-1)

CodePudding user response：

Use this formula Assuming E2 has Code and D2 has F-Name

=E2&"_"&LEFT(D2,FIND(" ",D2)-1)