Function that is not quite LEFT

  • Thread starter Thread starter Leanne M (Aussie)
  • Start date Start date
L

Leanne M (Aussie)

Hi,

I have a worksheet full of data and I need to split some data out of one
cell and put it in another.

I need to take everything left of the first - (dash) and put it in a column
beside the first column. The problem is the data in the column is not
consistent - ie there could be 2,3,4,5 digits before the dash.

Does anyone know a way I can do this. I have been using Left, starting with
the highest possible number and then filtering on the result column for
anything that contains a dash and reducing the number by one each time.

There has to be a quicker way than this.

Any ideas appreciated.
 
Try:

=IF(ISERROR(LEFT(A6,FIND("-",A6)-1)),"",LEFT(A6,FIND("-",A6)-1))

If there will *always* be a dash in the cell then you can just use:

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Do you know how many characters you want to take after the dash?

You can use the MID function, together with FIND, but post back and
I'll give you an appropriate formula.

Pete
 
Hi Leanne,

Use Data -> Text to Columns
Delimited
put '-' in the box next to Other delimiter
Finish

And what's an Aussie doing living in the UK?

Ed Ferrero
www.edferrero.com
 
Hi,

You may have to adjust the -1 to -2 if there is a space before the dash

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

Mike
 
You're very welcome. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top