BeginnerText Functions
Split a full name with LEFT and FIND
Pull the first name out of a single full-name column.
LEFT · FIND
7 min15 points
FIND
Returns the position of one text value inside another. Case-sensitive.
Syntax
FIND(find_text, within_text, [start_num])
Text Functions.
Practise FIND3 interactive exercises, free, no account needed
A worked example
=FIND(" ",A2)
Returns 7
Returns the position of the first space in Marcus Bell.
Where it goes wrong
FIND is case-sensitive and returns #VALUE! rather than 0 when the text is absent, so it needs wrapping if a miss is expected.
Consider instead: SEARCH, when casing should not matter or you need wildcards.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- This works for "Marcus Bell" because that name happens to be six letters. Fill it down and "Dana Whitfield" becomes "Dana W". You need to find where the space actually is.Split a full name with LEFT and FIND
- You have taken the comma along with the city, giving "Cleveland," instead of "Cleveland". FIND returns the comma's own position, so you want one character fewer.Split a city away from its state
Practise FIND
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
- IntermediateText Functions
Split a city away from its state
Cut a combined location field at the comma without counting characters by hand.
LEFT · FIND
8 min25 points - AdvancedText Functions
Extract a lot number from a messy code
Pull a fixed-length value out of the middle of a code that varies in shape.
MID · FIND
12 min35 points