Extract text from a string of text (amend formula to include new criteria)

Hi

I posted a question earlier to Extract text from a string of text which 'hlpplease' kindly answered see i.e. http://www.excelforum.com/excel-gene...g-of-text.html

However, whilst the formula =LEFT(A1,SEARCH(" -",A1)-1) works as required I've now realised that some of the data only has three characters anyway and therefore returns a #VALUE! in this case.

Is it possible to amend the formula to also check if there is not a – in the text and return the text as is

e.g.

NRB - LNG-LIC the expected output would be NRB

TRM ELECTRIC - BSG-INFS the expected output would be TRM ELECTRIC

TRM GAS - TRM-ASMN-ENI the expected output would be TRM GAS

TRM GAS the expected output would be TRM GAS

TRM ELECTRIC the expected output would be TRM ELECTRIC

NRB the expected output would be NRB


Any assistance would be greatly appreciated


Many thanks in advance

Rob



!

Rob,

Give this a try:

Please Login or Register  to view this content.

tigeravatar,

EXCELLENT just what I wanted !! You and 'hlpplease' are STARS

Many many thanks