Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 12th 12, 10:19 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 4
Default Cut Numbers Problem

Not exactly new to Excel but since retiring 5 yrs ago everything I knew (which wasn't much) has gone out the back door. I have researched this site and seen this question pop up in many ways but none of the solutions given work for what I am trying to do. So I will explain what I am doing and maybe that will help.

Here is what I am trying to do.

I listen to shortwave radio. I copy cut number Morse code stations. Cut numbers are numbers that have been shortened so it doesn't take as much time to send them. To cut a long story short (pun intended) short letters are used to represent numbers. Example:

A=1 N=2 D=3 U=4 W=5 R=6 I=7 G=8 M=9 T=0 (Those letters are chosen because they are short and easy to send.)

So to send the five number group 87390
I would send GIDMT

So as I sit at my desk in front of my radio copying these numbers (always in groups of five) it looks like this.

AMITU RIMWD NIMTA UNDWR TIWDA (and so on)

By now you have guessed where I am going. I want to copy five letters per cell, hit tab and copy the next five letters.......then when finished or even as I type I want the letters to convert to the numbers they represent.

Thanks for helping if you can. By the way, do you think this could be done in MS Word instead of Excel?

GER9999

  #2   Report Post  
Old July 13th 12, 03:49 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Cut Numbers Problem

On Thu, 12 Jul 2012 21:19:12 +0000, GER9999 wrote:


Not exactly new to Excel but since retiring 5 yrs ago everything I knew
(which wasn't much) has gone out the back door. I have researched this
site and seen this question pop up in many ways but none of the
solutions given work for what I am trying to do. So I will explain what
I am doing and maybe that will help.

Here is what I am trying to do.

I listen to shortwave radio. I copy cut number Morse code stations. Cut
numbers are numbers that have been shortened so it doesn't take as much
time to send them. To cut a long story short (pun intended) short
letters are used to represent numbers. Example:

A=1 N=2 D=3 U=4 W=5 R=6 I=7 G=8 M=9 T=0 (Those letters are chosen
because they are short and easy to send.)

So to send the five number group 87390
I would send GIDMT

So as I sit at my desk in front of my radio copying these numbers
(always in groups of five) it looks like this.

AMITU RIMWD NIMTA UNDWR TIWDA (and so on)

By now you have guessed where I am going. I want to copy five letters
per cell, hit tab and copy the next five letters.......then when
finished or even as I type I want the letters to convert to the numbers
they represent.

Thanks for helping if you can. By the way, do you think this could be
done in MS Word instead of Excel?

GER9999


To convert that five character string of letters, into a number, can be done with a formula.
With your five character string in A1,

=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),$I$1:$I$10 ,0)-1,10^{4,3,2,1,0})

where I1:I10 contain the letters above in the order matching zero to nine. In other words, T comes first.

T
A
V
D
U
W
R
I
G
M

For your other requirements.
As far as I know, it is not possible to convert the letters as you type them.
If you want the letters to be replaced after you tab to the next cell, you will need an event triggered VBA macro. Is that necessary?
I'd guess it's possible in Word using a macro.


  #3   Report Post  
Old July 14th 12, 07:39 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 4
Default

Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Thu, 12 Jul 2012 21:19:12 +0000, GER9999 wrote:


Not exactly new to Excel but since retiring 5 yrs ago everything I knew
(which wasn't much) has gone out the back door. I have researched this
site and seen this question pop up in many ways but none of the
solutions given work for what I am trying to do. So I will explain what
I am doing and maybe that will help.

Here is what I am trying to do.

I listen to shortwave radio. I copy cut number Morse code stations. Cut
numbers are numbers that have been shortened so it doesn't take as much
time to send them. To cut a long story short (pun intended) short
letters are used to represent numbers. Example:

A=1 N=2 D=3 U=4 W=5 R=6 I=7 G=8 M=9 T=0 (Those letters are chosen
because they are short and easy to send.)

So to send the five number group 87390
I would send GIDMT

So as I sit at my desk in front of my radio copying these numbers
(always in groups of five) it looks like this.

AMITU RIMWD NIMTA UNDWR TIWDA (and so on)

By now you have guessed where I am going. I want to copy five letters
per cell, hit tab and copy the next five letters.......then when
finished or even as I type I want the letters to convert to the numbers
they represent.

Thanks for helping if you can. By the way, do you think this could be
done in MS Word instead of Excel?

GER9999


To convert that five character string of letters, into a number, can be done with a formula.
With your five character string in A1,

=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),$I$1:$I$10 ,0)-1,10^{4,3,2,1,0})

where I1:I10 contain the letters above in the order matching zero to nine. In other words, T comes first.

T
A
V
D
U
W
R
I
G
M

For your other requirements.
As far as I know, it is not possible to convert the letters as you type them.
If you want the letters to be replaced after you tab to the next cell, you will need an event triggered VBA macro. Is that necessary?
I'd guess it's possible in Word using a macro.
Thanks Ron I will give that a try. I don't need the change to take place as I type in the same cell. Another location is fine.
  #4   Report Post  
Old July 14th 12, 05:11 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 4
Default

I am having trouble with this part of your suggestion:

where I1:I10 contain the letters above

Where is I1:I10 in the formula you gave? I'm lost as to where to put the letters T,A,N,D,U,W,R,I,G,M




Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Thu, 12 Jul 2012 21:19:12 +0000, GER9999 wrote:


Not exactly new to Excel but since retiring 5 yrs ago everything I knew
(which wasn't much) has gone out the back door. I have researched this
site and seen this question pop up in many ways but none of the
solutions given work for what I am trying to do. So I will explain what
I am doing and maybe that will help.

Here is what I am trying to do.

I listen to shortwave radio. I copy cut number Morse code stations. Cut
numbers are numbers that have been shortened so it doesn't take as much
time to send them. To cut a long story short (pun intended) short
letters are used to represent numbers. Example:

A=1 N=2 D=3 U=4 W=5 R=6 I=7 G=8 M=9 T=0 (Those letters are chosen
because they are short and easy to send.)

So to send the five number group 87390
I would send GIDMT

So as I sit at my desk in front of my radio copying these numbers
(always in groups of five) it looks like this.

AMITU RIMWD NIMTA UNDWR TIWDA (and so on)

By now you have guessed where I am going. I want to copy five letters
per cell, hit tab and copy the next five letters.......then when
finished or even as I type I want the letters to convert to the numbers
they represent.

Thanks for helping if you can. By the way, do you think this could be
done in MS Word instead of Excel?

GER9999


To convert that five character string of letters, into a number, can be done with a formula.
With your five character string in A1,

=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),$I$1:$I$10 ,0)-1,10^{4,3,2,1,0})

where I1:I10 contain the letters above in the order matching zero to nine. In other words, T comes first.

T
A
V
D
U
W
R
I
G
M

For your other requirements.
As far as I know, it is not possible to convert the letters as you type them.
If you want the letters to be replaced after you tab to the next cell, you will need an event triggered VBA macro. Is that necessary?
I'd guess it's possible in Word using a macro.
  #5   Report Post  
Old July 14th 12, 06:41 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 4
Default

OK, I have the formula working. A big thanks for that. Now one more request.

The way it works now when I type 5 letters in cell A1 the cell containing the formula changes the letters to numbers. These messages are usually 150 groups (read cells) long. Do I paste the formula 150 times into 150 cells each time changing A1 to B1, C1, D1 and so on? I hope I am clear on my meaning here. I want to be able to type 10 cells across then start a new line 10 cells across, etc. A1 through J1, then A2 through J2, then A3 through J3, etc.

Even if I have to copy/paste 150 times I have what I need and I thank you so much!
GE


Quote:
Originally Posted by GER9999 View Post
I am having trouble with this part of your suggestion:

where I1:I10 contain the letters above

Where is I1:I10 in the formula you gave? I'm lost as to where to put the letters T,A,N,D,U,W,R,I,G,M


  #6   Report Post  
Old July 14th 12, 09:12 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Cut Numbers Problem

On Sat, 14 Jul 2012 16:11:01 +0000, GER9999 wrote:

I am having trouble with this part of your suggestion:

where I1:I10 contain the letters above

Where is I1:I10 in the formula you gave?


=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),$I$1:$I$10 ,0)-1,10^{4,3,2,1,0})


$I$1:$I$10 refers to those cells using the Absolute Reference mode. See HELP for Absolute Reference for discussion as to different addressing modes. There is relative, mixed, and absolute.


I'm lost as to where to put
the letters T,A,N,D,U,W,R,I,G,M


Assuming you are using the A1 cell referencing mode, I1 refers to a particular cell on the sheet. It is the cell located in Column I / Row 1

So navigate to cell I1 and enter T.

I1: T
I2: A
I3: V
I4: D
I5: U
I6: W
I7: R
I8: I
I9: G
I10: M

You can put those letters in any ten contiguous cells that are in the same column.
You could also substitute the range reference in the formula with an array constant. I thought it was simpler to put the letters in a column than hard code them into the formula, but you could certainly do the latter:

=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),{"T";"A";" V";"D";"U";"W";"R";"I";"G";"M"},0)-1,10^{4,3,2,1,0})

Again, this assumes your 5 letter group is in A1. You will need to change the A1 reference depending on where your group is actually located.


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
Page numbers problem yugo23 Excel Worksheet Functions 1 June 14th 10 01:43 PM
Format numbers problem brodiemac Excel Discussion (Misc queries) 4 March 28th 08 01:20 PM
numbers problem markvr6 Excel Discussion (Misc queries) 2 February 19th 06 10:56 AM
Execl problem with numbers Peterg Excel Discussion (Misc queries) 1 October 17th 05 05:07 AM
Problem with ranking numbers Pati M Excel Worksheet Functions 1 November 23rd 04 11:29 PM


All times are GMT +1. The time now is 01:13 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017