Function that is not quite LEFT

[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.

 

[Sandy Mann]

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

 

[Pete_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

 

[Ed Ferrero]

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

 

[Mike H]

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

 

[Leanne M (Aussie)]

Yes there is always a dash in the data. Thank you this works great.

 

[Sandy Mann]

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