Extracting Text from a cell

W

Wendy L

Windows XP
Office XP

I would like a formula that will extract all but the last 10 characters from
a text string located in cell C2.

For example: My text string is an address line and the last 10 characters
will always be zipcode+4 like the following "Cheasapeake Bay, NC 85236-1452"
What I want the formula to do is return only the "Chesapeake Bay, NC"
portion and leave the 10 digit zipcode string out.
 
K

Kevin H. Stecyk

Wendy wrote...
Windows XP
Office XP

I would like a formula that will extract all but the last 10 characters from
a text string located in cell C2.

For example: My text string is an address line and the last 10 characters
will always be zipcode+4 like the following "Cheasapeake Bay, NC 85236-1452"
What I want the formula to do is return only the "Chesapeake Bay, NC"
portion and leave the 10 digit zipcode string out.
Click to expand...

Hi Wendy,

=LEFT(C2,LEN(C2)-11)

The formula says to take the left characters (excluding the zip code + 1
space) from C2.
Len(c2) gets the total length of chars in C2.

Hope that helps.

Kevin
 
E

Elma

Say your text string is in A1. You will need the following formula:

=(LEFT(A1,LEN(A1)-11))

The formual basically takes whatever the lenth is of the string, minuses 11
(zip + space) from that and then uses that in the LEFT function.

Good luck!
Elma
 
Top