many numbers in one cell separated by hyphen.

Many numbers in one cell separated by hyphen. How to put them in separate
rows of one column?

one way:

Choose Data/Text to Columns. Select the Delimited radio button and
choose Next. Enter a hyphen in the "Other" field, then click Finish.




In article <[email protected]>,
"baju" <[email protected]> wrote:

> Many numbers in one cell separated by hyphen. How to put them in separate
> rows of one column?

I would say the quickest way is to select the column and
do Data > Text to Columns, using "-" as your delimiter.
Then select the data, copy it, and Edit > Paste Special >
Transpose.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Many numbers in one cell separated by hyphen. How to
put them in separate
>rows of one column?
>
>.
>

And then choose the resulting values, copy, then paste-special transpose to
get them into a column.

HTH,
Bernie
MS Excel MVP

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> one way:
>
> Choose Data/Text to Columns. Select the Delimited radio button and
> choose Next. Enter a hyphen in the "Other" field, then click Finish.
>
>
>
>
> In article <[email protected]>,
> "baju" <[email protected]> wrote:
>
> > Many numbers in one cell separated by hyphen. How to put them in
separate
> > rows of one column?

There are too many numbers (more than 256) to put them in columns first.

"Bernie Deitrick" пишет:

> And then choose the resulting values, copy, then paste-special transpose to
> get them into a column.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > one way:
> >
> > Choose Data/Text to Columns. Select the Delimited radio button and
> > choose Next. Enter a hyphen in the "Other" field, then click Finish.
> >
> >
> >
> >
> > In article <[email protected]>,
> > "baju" <[email protected]> wrote:
> >
> > > Many numbers in one cell separated by hyphen. How to put them in
> separate
> > > rows of one column?
>
>
>

OK, try this. Assuming your value is in A1.

1. Put this in A2 and copy down until you get an error=20
value:

=3DRIGHT(A1,LEN(A1)-FIND("-",A1))

2. Put this in B1 and fill all the way down:

=3DIF(ISERROR(FIND("-",A1)),A1,LEFT(A1,FIND("-",A1)-1))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>There are too many numbers (more than 256) to put them=20
in columns first.
>
>"Bernie Deitrick" =D0=BF=D0=B8=D1^=D0=B5=D1,:
>
>> And then choose the resulting values, copy, then paste-
special transpose to
>> get them into a column.
>>=20
>> HTH,
>> Bernie
>> MS Excel MVP
>>=20
>> "JE McGimpsey" <[email protected]> wrote in message
>> news:jemcgimpsey-
[email protected]...
>> > one way:
>> >
>> > Choose Data/Text to Columns. Select the Delimited=20
radio button and
>> > choose Next. Enter a hyphen in the "Other" field,=20
then click Finish.
>> >
>> >
>> >
>> >
>> > In article <CA641783-057C-4AC2-BFD3-
[email protected]>,
>> > "baju" <[email protected]> wrote:
>> >
>> > > Many numbers in one cell separated by hyphen. How =20
to put them in
>> separate
>> > > rows of one column?
>>=20
>>=20
>>=20
>.
>

Thank you, that works.

"Jason Morin" пишет:

> OK, try this. Assuming your value is in A1.
>
> 1. Put this in A2 and copy down until you get an error
> value:
>
> =RIGHT(A1,LEN(A1)-FIND("-",A1))
>
> 2. Put this in B1 and fill all the way down:
>
> =IF(ISERROR(FIND("-",A1)),A1,LEFT(A1,FIND("-",A1)-1))
>
> HTH
> Jason
> Atlanta, GA
>