CODEWITHSUNDEEP
excelexcel-formula
SIM
SIM

Reputation: 22440

Can't get the first part of a text from a string using formula

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

Upvotes: 0

Views: 66

Answers (2)

user4039065
user4039065

Reputation:

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.

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152465

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)

Upvotes: 3

Related Questions