Hi,

I have a column of text

ny17
ca13
ky04

I want to be able to split the text and numbers into two separate columns,
"ny" in one, "17" in another. As you can see there's no delimiter. Since I
know excel can recognize the difference between text and a number I know
there's a way to do it but I don't know what it is.

Alternatively, I would be happy to find a way to insert a delimiter like a
comma between the text and number to use "text to columns".

Can anyone help?
Thanks,
Rebecca

You show two letters and two numbers. for these:

=LEFT(A1,2)
and
=RIGHT(A1,2)
--
Gary''s Student - gsnu200738


"rebc" wrote:

> Hi,
>
> I have a column of text
>
> ny17
> ca13
> ky04
>
> I want to be able to split the text and numbers into two separate columns,
> "ny" in one, "17" in another. As you can see there's no delimiter. Since I
> know excel can recognize the difference between text and a number I know
> there's a way to do it but I don't know what it is.
>
> Alternatively, I would be happy to find a way to insert a delimiter like a
> comma between the text and number to use "text to columns".
>
> Can anyone help?
> Thanks,
> Rebecca

If the text is always 2 characters, you can still use Data|text to columns. But
choose fixed width--not delimited.



rebc wrote:
>
> Hi,
>
> I have a column of text
>
> ny17
> ca13
> ky04
>
> I want to be able to split the text and numbers into two separate columns,
> "ny" in one, "17" in another. As you can see there's no delimiter. Since I
> know excel can recognize the difference between text and a number I know
> there's a way to do it but I don't know what it is.
>
> Alternatively, I would be happy to find a way to insert a delimiter like a
> comma between the text and number to use "text to columns".
>
> Can anyone help?
> Thanks,
> Rebecca

--

Dave Peterson

Well, that was embarrassingly easy. Thanks for making my day!

RC

"Gary''s Student" wrote:

> You show two letters and two numbers. for these:
>
> =LEFT(A1,2)
> and
> =RIGHT(A1,2)
> --
> Gary''s Student - gsnu200738
>
>
> "rebc" wrote:
>
> > Hi,
> >
> > I have a column of text
> >
> > ny17
> > ca13
> > ky04
> >
> > I want to be able to split the text and numbers into two separate columns,
> > "ny" in one, "17" in another. As you can see there's no delimiter. Since I
> > know excel can recognize the difference between text and a number I know
> > there's a way to do it but I don't know what it is.
> >
> > Alternatively, I would be happy to find a way to insert a delimiter like a
> > comma between the text and number to use "text to columns".
> >
> > Can anyone help?
> > Thanks,
> > Rebecca

that was even easier! thanks to everyone!

"Dave Peterson" wrote:

> If the text is always 2 characters, you can still use Data|text to columns. But
> choose fixed width--not delimited.
>
>
>
> rebc wrote:
> >
> > Hi,
> >
> > I have a column of text
> >
> > ny17
> > ca13
> > ky04
> >
> > I want to be able to split the text and numbers into two separate columns,
> > "ny" in one, "17" in another. As you can see there's no delimiter. Since I
> > know excel can recognize the difference between text and a number I know
> > there's a way to do it but I don't know what it is.
> >
> > Alternatively, I would be happy to find a way to insert a delimiter like a
> > comma between the text and number to use "text to columns".
> >
> > Can anyone help?
> > Thanks,
> > Rebecca
>
> --
>
> Dave Peterson
>