Turning 1 into One

  • Thread starter Thread starter njhagan
  • Start date Start date
N

njhagan

(MS Excel 2000)

Hi,
Can I type "1" into a cell and get it automatically to come up as a
"One" in another cell. Similarly can I type "2" into the same cell to
come up as "Two" in the other? ... and if so how?

A juicy red apple is nice but not every apple is red!

http://www.wattersons.co.uk
 
njhagan,

I'm not sure that this is what you want but if you enter :
=LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10},{"One","Two","Three","Four","Five","Six","
Seven","Eight","Nine","Ten"})
in cel B1 and 1;2;...10 in A1, B1 will show One; Two ; ....Ten in B1

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi,

I'm not sure if this is the ticket for you, but take a look..

I would create a lookup table with the numbers to be recognised and the
TEXT for each next to the value. Then I would create a vlookup to
lookup the entered number and fetch the fully spealt version..

terrible explanation, sorry.

take a look at the attached file which demonstrates converting the
number entered into column A into the full text equiv in column C.
The lookup table values are in columns E & F
and I also put a Data, Validation to only allow numbers between 1 - 15
to be entered in column A, as this is the extent of my number range in
the test.

Hope this helps.

MSG to XL Pros..
There are always 100's of ways to achieve results in Excel, please feel
free to show us a more graceful way ;-)

Regards

Attachment filename: convert_1_to_one__example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=416722
 
njhagan,

I'm not sure that this is what you want but if you enter :
=LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10},{"One","Two","Three","Four","Five","Six","
Seven","Eight","Nine","Ten"})
in cel B1 and 1;2;...10 in A1, B1 will show One; Two ; ....Ten in B1

NB It might be that this post arrives twice. I did send it earlier, but
somehow that didn't (yet) arrive.
--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Thanks Tony for taking time to answer my question.

Your answer is excellent but I have a problem. If I want to enter
zero (eg 100.85 or 432.07) it will not bring up ZERO in the appropriat
cell. I have changed ten and 10 in the lookup table to zero and 0 bu
in the formula the reference is given as >0. I guess this is where th
problem lies. Can you give me a further solution.

Once again hearty thanks,

Nev
 
Wow

nice one A.A.. that is a graceful line of sweet code. and well put.

njhagan,
are you looking to have it spell the numbers like this?

[you enter]
Cell A1 = 100.85

[lookup formulas display]

Cell C1 = One
Cell D1 = Zero
Cell E1 = Zero
Cell F1 = Eight
Cell G1 = Five

Please clarify your needs for me and I will try to assist,

my email door is always open @
(e-mail address removed)

Regs

Ton
 
NJHAGAN

I Just re-read your msg and suggest perhaps.
in my example Cell C2 formula changes to...
=IF(A2<>"",VLOOKUP(A2,$E$2:$F$11,2),"")

and the loopup table be changed to start at 0 Zero thu 9 Nine.

or << Reccomended >> use A.W.J. Ales fab formula as follows...

=LOOKUP(A1,{0,1,2,3,4,5,6,7,8,9},"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})


or is there more to your plan.

Regs

Ton
 
Very much thanx 2 u both for your help. I have used the AWJ Ales metho
but skyways would work OK too.

Don't eat the yellow snow!
 
Njhagan,

Thanks for the reply and oke I won't (eat that snow).

BTW : You can in my suggestion replace the two list {1;2;...;10} and {"One";
"Two"; ...."Ten"} with two ranges (either in a column or in a row) as well.
In that way the array can a bit easier be extended if necessary.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top