93245 - descr-ip24tion: Q-how do i only extract 93245?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisV - 27 Mar 2008 02:08 GMT

Hi,

I have a number and a description (which may at times also contain a number
as well as -) within a cell in excel separated by a - . How do i extract
only the number at the front as in the subject. Numbers are of varying
lengths.

Regards,

Chris

Reply to this Message

Pete_UK - 27 Mar 2008 02:22 GMT

Assuming your data is in A1, use this to extract the number before the
hyphen:

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

Your example has a space before the hyphen, so TRIM will get rid of
that. Multiplying by 1 converts the result to a number, but you can
leave this off if you want it as a text value. Copy the formula down
as required.

Hope this helps.

Pete

> Hi,
>
[quoted text clipped - 6 lines]
>
> Chris

Reply to this Message

Tyro - 27 Mar 2008 02:28 GMT

If there is always a "-" (dash) after the number you could use

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

That will give you a text string.
If you want a numeric string then

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

Tyro

> Hi,
>
[quoted text clipped - 7 lines]
>
> Chris

Reply to this Message

akphidelt - 27 Mar 2008 02:29 GMT

Go To

Data-->Text-to-Columns

Select Delimited, press next

Then click the checkbox Other: and type in -

Then press finish

> Hi,
>
[quoted text clipped - 6 lines]
>
> Chris

Reply to this Message

Gord Dibben - 28 Mar 2008 01:06 GMT

Before you press Finish, select the columns you don't want and "Do not import
column - skip"

Gord Dibben MS Excel MVP

>Go To
>
[quoted text clipped - 16 lines]
>>
>> Chris

Reply to this Message