Selecting data to the left of a specific character

  • Thread starter Thread starter Phil Amey
  • Start date Start date
P

Phil Amey

Hi,

I have a series of data in a column which are a combination of a number separated from text with a space then a hyphen and then another space -
e.g. 50120 - Deming

The numbers are not always going to be resticted to 5 characters, in time they wil be 6 then 7 ans so on.

How do you identify the space after the numbers and then select only the number to the left.

Kindest Regards - Philip Amey
 
Phil,

Try a formula like the following:

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Phil Amey said:
Hi,

I have a series of data in a column which are a combination of
Click to expand...
a number separated from text with a space then a hyphen and then
another space -
e.g. 50120 - Deming

The numbers are not always going to be resticted to 5
Click to expand...
characters, in time they wil be 6 then 7 ans so on.
 
Philip,

Use the FIND worksheet function in combination with LEFT
function...

If A1 = "987654 - xxxxxx"
=VALUE(LEFT(A1,FIND(" ",A1)-1))

Cheers,
Dave
-----Original Message-----
Hi,

I have a series of data in a column which are a
Click to expand...
combination of a number separated from text with a space
then a hyphen and then another space -
e.g. 50120 - Deming

The numbers are not always going to be resticted to 5
Click to expand...
characters, in time they wil be 6 then 7 ans so on.
How do you identify the space after the numbers and then
Click to expand...
select only the number to the left.
 
Back
Top