Splitting with LEFT, MID and FIND
1 / 5
Each product code is a category, a dash, then a colour. Pull the colour out into column B.
keyboard Typing formulas is easiest on a computer with a keyboard.
A1
| A | B | C | |
|---|---|---|---|
| 1 | Product code | Colour | |
| 2 | SWEATER-RED | ||
| 3 | HOODIE-ORANGE | ||
| 4 | SHIRT-NAVY | ||
| 5 | BAG-RED | ||
| 6 | HAT-NAVY | ||
| 7 | GLOVES-BLUE | ||
| 8 | JACKET-ORANGE | ||
| 9 | SWEATER-GREY | ||
| 10 | |||
| 11 | |||
| 12 | |||
| 13 |
codes
colour
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
How LEFT and MID and RIGHT and FIND works
How do
LEFT,RIGHTandMIDwork?=LEFT(A2,2)takes the first 2 characters.=RIGHT(A2,3)takes the last 3.=MID(A2,4,2)takes 2 characters, starting at character 4.
What if the parts change length?
In codes like CAP-BLUE and JACKET-RED the parts have different lengths, so a fixed number won't work for every row.
FINDgives the position of a character, such as the dash:=FIND("-",A2).How do you get the part before the dash?
It is the dash's position minus 1 characters long:
=LEFT(A2,FIND("-", A2)-1) How do you get the part after the dash?
It starts one character after the dash.
MIDstops at the end of the text, so asking for more characters than are left is fine:=MID(A2,FIND("-", A2)+1, 20) Newer Excel also has
TEXTBEFOREandTEXTAFTER, which do each in one step:=TEXTAFTER(A2,"-").
Know someone fighting with a spreadsheet? Send them this page.