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?

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" wrote in message
...
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?

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" wrote in message
...
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?

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?

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" wrote in message
...
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?

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" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
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" 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 ?

"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.

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))

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" wrote in message
...
"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.

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))

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" wrote in message
...
"Ken Wright" 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 ?

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" wrote in message
...
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" wrote in message
...
"Ken Wright" 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 ?

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" wrote in message
...
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" wrote in message
...
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?

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" wrote in message
...
"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.

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))

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?