Remember Me?

#1
January 7th 05, 09:49 PM
 ezu Posts: n/a
convert cells containing alpha to numeric

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?

#2
January 7th 05, 10:14 PM
 Ken Wright Posts: n/a

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?

#3
January 7th 05, 10:38 PM
 Arvi Laanemets Posts: n/a

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?

#4
January 8th 05, 04:07 AM
 Ashish Mathur Posts: n/a

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?

#5
January 8th 05, 11:09 AM
 Ken Wright Posts: n/a

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?

#6
January 8th 05, 02:10 PM
 RagDyeR Posts: n/a

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?

#7
January 8th 05, 02:15 PM
 RagDyeR Posts: n/a

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?

#8
January 9th 05, 09:06 AM
 Harlan Grove Posts: n/a

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

#9
January 9th 05, 09:09 AM
 Harlan Grove Posts: n/a

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

#10
January 9th 05, 10:26 AM
 Ragdyer Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM Monty Excel Worksheet Functions 0 December 18th 04 10:25 PM

All times are GMT +1. The time now is 02:50 PM.