Can't get the first part of a text from a stri

问题:

I've written a formula to get the first part of some text separated by space from a string located in cell A1. However, it doesn't work. Am I doing something wrong with my formula? Thanks in advance.

This is the main string:

HOUSTON TX 77017-2328

The formula i've tried with:

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

The output I'm expecting:

HOUSTON

回答1:

You have a stylized space: char(160) versus char(32)

You can either replace all those with normal spaces or use this formula:

=LEFT(A1,FIND(CHAR(160),A1)-1)

回答2:

To accommodate zip and zip+4 use,

=REPLACE(A1, LEN(A1)-IF(CODE(RIGHT(A1, 5))=45, 13, 8), LEN(A1), TEXT(,))

Assuming a two character state abbreviation.