Extracting Text from a cell

[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.

 

[Frank Kabel]

Hi
use
=LEFT(A1,LEN(A1)-10)

 

[Wendy L]

Thank you Frank, I knew it was something simple! Worked perfectly.

 

[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.

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

 

[Guest]

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

 

[Chip Pearson]

Wendy,

Use a formula like

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com