convert cells containing alpha to numeric

[ezu]

In EXCEL XP PRO I am trying to write a formula to convert cells containing a
letter to a number and am having little success. i.e. A1005 to 11005 or
B1273 to 21273.

Can you enlighten me?

[Ken Wright]

Always a single letter and at the start?

=--RIGHT(A1,LEN(A1))

or select all data and do Data / Text to Columns Fixed Width / Set the break
after the first character.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"ezu" <[email protected]> wrote in message
news:[email protected]...
> In EXCEL XP PRO I am trying to write a formula to convert cells containing
a
> letter to a number and am having little success. i.e. A1005 to 11005 or
> B1273 to 21273.
>
> Can you enlighten me?

[Arvi Laanemets]

Hi

There isn't enough info for exact answer. Start the source string always
with letter, or can it be simply numeric too. Can there be 2 or 3 or ...
characters at start. Etc.

For case the leftmost character will always be a letter, and rest of string
characters are numbers, the next formulas will do (with string in cell A2)

=RIGHT(A2,LEN(A2)-1)
or
MID(A2,2,99)

(you can replace the number 99 in second formula with any big enough - it
must be at least as much as the length of longest string)

Arvi Laanemets


"ezu" <[email protected]> wrote in message
news:[email protected]...
> In EXCEL XP PRO I am trying to write a formula to convert cells containing
a
> letter to a number and am having little success. i.e. A1005 to 11005 or
> B1273 to 21273.
>
> Can you enlighten me?

[Ashish Mathur]

Hi,

Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter) the
following formula in cell B1.

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5),1)),0),COUNT(1*MID(A1,ROW($1:$15),1)))

You should get only the numeric portion irrespective of the number of text
characters inthe beginning.

Regards,

Ashish Mathur

"ezu" wrote:

> In EXCEL XP PRO I am trying to write a formula to convert cells containing a
> letter to a number and am having little success. i.e. A1005 to 11005 or
> B1273 to 21273.
>
> Can you enlighten me?

[Ken Wright]

Slightly shorter version

=--MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:15")),1)),0),LEN(A1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ashish Mathur" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter) the
> following formula in cell B1.
>
>
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5),1)),0),COUNT(1*MID(A1,ROW(
$1:$15),1)))
>
> You should get only the numeric portion irrespective of the number of text
> characters inthe beginning.
>
> Regards,
>
> Ashish Mathur
>
> "ezu" wrote:
>
> > In EXCEL XP PRO I am trying to write a formula to convert cells
containing a
> > letter to a number and am having little success. i.e. A1005 to 11005
or
> > B1273 to 21273.
> >
> > Can you enlighten me?

[RagDyeR]

Following your scenario, that you want A = 1 and B = 2,
I assume you want Z = 26.

If that's the case, I'd guess that the easiest way would be to create a
list, in an out of the way area of your sheet, and have a formula refer to
the list to find the exact value for the first letter in the cell, and then
append the numeric portion to that value.

In Z1 to Z26, enter the letters of the alphabet.

Then, assuming your data list started in A1,
Enter this formula in B1 and drag down to copy as needed:

=--(MATCH(LEFT(A1),$Y$1:$Y$26)&RIGHT(A1,LEN(A1)-1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"ezu" <[email protected]> wrote in message
news:[email protected]...
In EXCEL XP PRO I am trying to write a formula to convert cells containing a
letter to a number and am having little success. i.e. A1005 to 11005 or
B1273 to 21273.

Can you enlighten me?

[RagDyeR]

Sorry, wrong column !

Use this:

=--(MATCH(LEFT(A1),$Z$1:$Z$26)&RIGHT(A1,LEN(A1)-1))


HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:%[email protected]...
Following your scenario, that you want A = 1 and B = 2,
I assume you want Z = 26.

If that's the case, I'd guess that the easiest way would be to create a
list, in an out of the way area of your sheet, and have a formula refer to
the list to find the exact value for the first letter in the cell, and then
append the numeric portion to that value.

In Z1 to Z26, enter the letters of the alphabet.

Then, assuming your data list started in A1,
Enter this formula in B1 and drag down to copy as needed:

=--(MATCH(LEFT(A1),$Y$1:$Y$26)&RIGHT(A1,LEN(A1)-1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




"ezu" <[email protected]> wrote in message
news:[email protected]...
In EXCEL XP PRO I am trying to write a formula to convert cells containing a
letter to a number and am having little success. i.e. A1005 to 11005 or
B1273 to 21273.

Can you enlighten me?

[Harlan Grove]

"Ken Wright" <[email protected]> wrote...
>Always a single letter and at the start?
>
>=--RIGHT(A1,LEN(A1))
....

?!

How does this differ from =--LEFT(A1,LEN(A1)) or just =--A1 ?

[Harlan Grove]

"ezu" <[email protected]> wrote...
>In EXCEL XP PRO I am trying to write a formula to convert cells containing
>a letter to a number and am having little success. i.e. A1005 to 11005
>or B1273 to 21273.

If only the initial character would be nonnumeric, and only A-I, they you
could try

=--((CODE(UPPER(LEFT(x,1))-64)&MID(x,2,1024))

[Ragdyer]

Neat !
Have to remember that one.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "ezu" <[email protected]> wrote...
> >In EXCEL XP PRO I am trying to write a formula to convert cells
containing
> >a letter to a number and am having little success. i.e. A1005 to 11005
> >or B1273 to 21273.
>
> If only the initial character would be nonnumeric, and only A-I, they you
> could try
>
> =--((CODE(UPPER(LEFT(x,1))-64)&MID(x,2,1024))
>
>

[Ken Wright]

Dohhhhhhhhhh - you got me Harlan

=--RIGHT(A1,LEN(A1)-1) was what I meant :-(

Cheers for the catch.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Ken Wright" <[email protected]> wrote...
> >Always a single letter and at the start?
> >
> >=--RIGHT(A1,LEN(A1))
> ...
>
> ?!
>
> How does this differ from =--LEFT(A1,LEN(A1)) or just =--A1 ?
>
>

[Ken Wright]

Aaagghhhhhhhhhh - Just saw your other post and sat there thinking 'Why would
Harlan use all that just to get rid of an initial letter?"
.......................... then I read the damn question
properly!!!!!!!!!!!!!!!!

Didn't see the bit where the first letter got converted to something else
:-(

( Going back to bed now I think - Will try getting up again <g> )

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Dohhhhhhhhhh - you got me Harlan
>
> =--RIGHT(A1,LEN(A1)-1) was what I meant :-(
>
> Cheers for the catch.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Harlan Grove" <[email protected]> wrote in message
> news:%[email protected]...
> > "Ken Wright" <[email protected]> wrote...
> > >Always a single letter and at the start?
> > >
> > >=--RIGHT(A1,LEN(A1))
> > ...
> >
> > ?!
> >
> > How does this differ from =--LEFT(A1,LEN(A1)) or just =--A1 ?
> >
> >
>
>

[Ken Wright]

Please ignore the post from the guy that didn't read the damn question
properly (ie me) :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Slightly shorter version
>
>
=--MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:15")),1)),0),LEN(A1))
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Ashish Mathur" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter) the
> > following formula in cell B1.
> >
> >
>
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5),1)),0),COUNT(1*MID(A1,ROW(
> $1:$15),1)))
> >
> > You should get only the numeric portion irrespective of the number of
text
> > characters inthe beginning.
> >
> > Regards,
> >
> > Ashish Mathur
> >
> > "ezu" wrote:
> >
> > > In EXCEL XP PRO I am trying to write a formula to convert cells
> containing a
> > > letter to a number and am having little success. i.e. A1005 to 11005
> or
> > > B1273 to 21273.
> > >
> > > Can you enlighten me?
>
>

[Ken Wright]

LOL - Ok let me try and retain at least a tad of dignity by picking you up
on a syntax error Harlan :-)

Missing paren before -64 and no need for the arg in the LEFT function <g>

=--((CODE(UPPER(LEFT(A1)))-64)&MID(A1,2,1024))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "ezu" <[email protected]> wrote...
> >In EXCEL XP PRO I am trying to write a formula to convert cells
containing
> >a letter to a number and am having little success. i.e. A1005 to 11005
> >or B1273 to 21273.
>
> If only the initial character would be nonnumeric, and only A-I, they you
> could try
>
> =--((CODE(UPPER(LEFT(x,1))-64)&MID(x,2,1024))
>
>

[Ragdyer]

But Ken,
You'll notice you weren't the only one!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> Please ignore the post from the guy that didn't read the damn question
> properly (ie me) :-(
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> > Slightly shorter version
> >
> >
>
=--MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:15")),1)),0),LEN(A1))
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
> --
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
> --
> >
> > "Ashish Mathur" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter)
the
> > > following formula in cell B1.
> > >
> > >
> >
>
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5),1)),0),COUNT(1*MID(A1,ROW(
> > $1:$15),1)))
> > >
> > > You should get only the numeric portion irrespective of the number of
> text
> > > characters inthe beginning.
> > >
> > > Regards,
> > >
> > > Ashish Mathur
> > >
> > > "ezu" wrote:
> > >
> > > > In EXCEL XP PRO I am trying to write a formula to convert cells
> > containing a
> > > > letter to a number and am having little success. i.e. A1005 to
11005
> > or
> > > > B1273 to 21273.
> > > >
> > > > Can you enlighten me?
> >
> >
>
>