Is there a formula or way to extract a specific string format from a cell?

[cubangt]

So i have a column in a report that for the most part has a structured string value but on certain rows and records, that string does not exist.. so when i apply a LEFT formula on the new column to extract that string, i get alot of chopped up values and rather have the cell empty..

So i have this in our raw data column with a value structures like this:

STR4552 -Alexandria LA. (Alexandria,LA)
STR4537 - Lake Charles LA (Lake Charles, LA)
Elizabethtown
USA

is this a safe formula to use to accomplish what im trying to do? my initial testing seems to look correct, im just curious if this is the best option. in the above sample data, i only want the new column to contain the STR value and if it doesn exist, then i want the cell empty.

=IF(ISNUMBER(SEARCH("STR",D2)),LEFT(D2,6),"")

[Glenn Kennedy]

It all depends on what variations exist in your data. BtW, you probably meant:

=IF(ISNUMBER(SEARCH("STR",A2)),LEFT(A2,7),"")

Safe enough... providing that STR can not occur elsewhere/earlier in the string. "Strictly speaking"... will return "Strictl" and "Astrid's house STR4552 -Alexandria LA. (Alexandria,LA)" will not return what you want.

Street, strip, strange, etc, all have the potential to be a problem... but it's all down to the raw data. Does it ALWAYS start with STR if STR is present?

A few more representative samples (in an Excel file) would help, if needed.

[cubangt]

So yes if STR is in the cell, it will always be the beginning of that cell.. so MAYBE if there is a way to check if STR is the first part of the cell, then that would help cut down on those other occurances?