Separating info in a cell

Topic

New Reply

HI,

I’ve attached a spreadsheet with 2 columns. The first column contains the address inluding the street or PO number. The user wants only the street name. I’ll used the following formula but its not yielding the correct results. What am I missing?

=RIGHT(A2,(LEN(A2)-1))

Thanks,
Leesha

Reply | Quote

Replies

Use this array formula (enter using control+shift+enter):

=IF(ISNUMBER(VALUE(LEFT(A2,1))),MID(A2,FIND(” “,A2),LEN(A2)),LEFT(A2,MAX(IF(ISERROR(FIND(” “,A2,ROW($1:$1000))),0,ROW($1:$1000)))))

It first checks if the first character is a number and if so, returns the content of the cell starting from the first space. If the first character is NOT a number, it returns everything up to the LAST space in the cell.

Reply | Quote

Perfect!!!! Thank you!
Leesha

Reply | Quote