Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 05, 09:49 PM
ezu
 
Posts: n/a
Default 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   Report Post  
Old January 7th 05, 10:14 PM
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Old January 7th 05, 10:38 PM
Arvi Laanemets
 
Posts: n/a
Default

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   Report Post  
Old January 8th 05, 04:07 AM
Ashish Mathur
 
Posts: n/a
Default

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   Report Post  
Old January 8th 05, 11:09 AM
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Old January 8th 05, 02:10 PM
RagDyeR
 
Posts: n/a
Default

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   Report Post  
Old January 8th 05, 02:15 PM
RagDyeR
 
Posts: n/a
Default

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   Report Post  
Old January 9th 05, 09:06 AM
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Old January 9th 05, 09:09 AM
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Old January 9th 05, 10:26 AM
Ragdyer
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert data type of cells to Text,Number,Date and Time Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
Convert Numeric into Text Monty Excel Worksheet Functions 0 December 18th 04 10:25 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017