Part of a text: LEFT, MID, FIND

A letter to a client needs their first name, the warehouse needs the item number, and the total needs the quantity. But an export often puts all of this into cells mixed with other words. Here's what it looks like:

Copying the piece you need from every row by hand takes a long time, and with a new export you'd have to start over. A formula does it by itself and recalculates when the data changes.

A piece by character numbers

Every character in a cell has a number in order, and spaces count too. Three functions take a piece of text by these numbers:

• LEFT(text,count) takes characters from the start;
• RIGHT(text,count) takes characters from the end;
• MID(text,start,count) takes characters from the middle, beginning with the number "start".

In this export, the item number always sits in the same place: five digits starting with the sixth character. So =MID(B2,6,5) is enough for it.

Up to the space: FIND

That won't work for the first name: "Daniel" has six letters, and "Elizabeth" has nine. But the first name always ends right before the first space. The FIND function finds the number of that space: the first argument is what to look for, the second is the cell. The space goes in quotes, like any text in a formula. For "Daniel Smith", the formula =FIND(" ",A2) returns 7: six letters of the first name, and the space comes seventh.

The first name takes up everything to the left of the space, that is, one character less than its number. If the space number is in B2, =LEFT(A2,B2-1) gives the first name. It's handy to put the number in a separate column first: that way you can see what FIND found.

Once the formula works, you can remove the extra column and put FIND right in place of B2:

=LEFT(A2,FIND(" ",A2)-1)

You can search for any character, not just a space. FIND(",",A2) finds the comma in the address "Austin, 12 Main St", and FIND("@",A2) finds the @ sign in an email address. If the character isn't in the text, FIND returns the #VALUE! error. You can build a check on this: the ISNUMBER function answers TRUE only when it gets a number, so ISNUMBER(FIND("@",A2)) shows TRUE for an address with the @ sign and FALSE for one without it.

Emma's first name came from the export in capital letters: "EMMA". The PROPER function brings it back to normal. It makes the first letter of each word uppercase and the rest lowercase. If "EMMA" is in C3, =PROPER(C3) returns "Emma".

A number from text: VALUE

The quantity in the export is written with a label: "12 pcs.". The formula up to the space has already pulled the digits into column B, but SUM in B6 counted 0:

LEFT, RIGHT and MID always return text, even if it's all digits. That's why the digits in column B are aligned to the left edge, like any text, and SUM skips them. The VALUE function turns text made of digits into a real number, and it moves to the right edge.

You can write both functions in one formula:

=VALUE(LEFT(A2,FIND(" ",A2)-1))

Read it from the inside out: FIND finds the space, LEFT takes the digits before it, and VALUE makes a number out of them.

Instead of cutting the label off, you can also erase it. The SUBSTITUTE function replaces some characters in the text with others, and if you replace " pcs." with empty quotes, "12 pcs." becomes "12". Then you don't need to work out where the space is:

=VALUE(SUBSTITUTE(A2," pcs.",""))