replace characters by numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have columns of characters that I do import from a scientific program.
For example cells A1 to A






In order to further work with the data I want to replace the characters by numbers (A=1; B=2; C=3; D=4)
In my example cells A1 to A4 should now have






I would like to do the task with a vba-macro that selects cells A1-A4 and then replaces the characters by numbers according to the above mentioned correlation (A=1; B=2; C=3; D=4). Does anyone know how to write this vba-macro

Thanks a lo
Michael
 
Sub convertText()
Dim rng As Range, cell As Range
Set rng = Range("A1:A4")
For Each cell In rng
cell.Value = Asc(Left(cell.Value, 1)) - 64
Next
End Sub

--
Regards,
Tom Ogilvy



Michael E. said:
Hi,

I have columns of characters that I do import from a scientific program.
For example cells A1 to A4

B
C
A
D

In order to further work with the data I want to replace the characters by numbers (A=1; B=2; C=3; D=4).
In my example cells A1 to A4 should now have:

2
3
1
4

I would like to do the task with a vba-macro that selects cells A1-A4 and
Click to expand...
then replaces the characters by numbers according to the above mentioned
correlation (A=1; B=2; C=3; D=4). Does anyone know how to write this
vba-macro?
 
not very efficient but will work:

Public Sub LettersToNumbers()
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Selection
With rCell
.Value = Asc(UCase(.Text)) - 64
End With
Next rCell
Application.ScreenUpdating = True
End Sub
 
Thanks for the reply
does the macro also work if the correlation between characters and numbers is different
e.g.: A=2; B=1; C=4; D=

Thanks for your hel
Michae

----- JE McGimpsey wrote: ----

not very efficient but will work

Public Sub LettersToNumbers(
Dim rCell As Rang
Application.ScreenUpdating = Fals
For Each rCell In Selectio
With rCel
.Value = Asc(UCase(.Text)) - 6
End Wit
Next rCel
Application.ScreenUpdating = Tru
End Su
 
No - for that you'd need a lookup table or an algorithm. What
correlations do you expect to see?
 
Public Sub LettersToNumbers()
Dim rCell As Range
vArr = Evaluate("{1,2;2,1;3,4;4,3}")
Application.ScreenUpdating = False
For Each rCell In Selection
With rCell
.Value = vArr(Asc(UCase(.Text)) - 64, 2)
End With
Next rCell
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


Michael E. said:
I think a simple lookup table would do, because the replacement of
Click to expand...
characters by numbers is mainly for statistic reasons. It would be of great
help for me if you could include a lookup table (A=2; B=1; C=4; D=3) in your
vba-macro.
 
Hi Tom

thank you very much. One question: sometimes the replacement logic is different, in this case two characeters should be replaced by one numer, e.g.((A2=2; B1=1; C6=4; D4=3). How do I have to change your macro

Kind regards
Michae

----- Tom Ogilvy wrote: ----

Public Sub LettersToNumbers(
Dim rCell As Rang
vArr = Evaluate("{1,2;2,1;3,4;4,3}"
Application.ScreenUpdating = Fals
For Each rCell In Selectio
With rCel
.Value = vArr(Asc(UCase(.Text)) - 64, 2
End Wit
Next rCel
Application.ScreenUpdating = Tru
End Su

--
Regards
Tom Ogilv


Michael E. said:
I think a simple lookup table would do, because the replacement o
Click to expand...
characters by numbers is mainly for statistic reasons. It would be of grea
help for me if you could include a lookup table (A=2; B=1; C=4; D=3) in you
vba-macro
Thanks a lo Michae
----- JE McGimpsey wrote: ----
No - for that you'd need a lookup table or an algorithm. Wha
Click to expand...
correlations do you expect to see
Click to expand...
 
That is a completely different problem. It may appear the same to you, but
in the context of the approach taken by both JE and myself, we took
advantage of the ascii value of the single character. A more general
approach would put a table on another worksheet such as
col a col b
AA 1
AB 2
BB 3
CA 4
RM 5

Name this Table Data1

then the code can be modified as:

Public Sub LettersToNumbers()
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Selection
With rCell
res = Application.Vlookup(.Value,Range("Data1"),2,0)
if not iserror(res) then
.Value = res
End if
End With
Next rCell
Application.ScreenUpdating = True
End Sub

As long as your table includes entries for anything you will find in the
range you are processing (and its replacement value), then the above
approach will work (single letters, double letters, words, sentences,
numbers - mixes of these).

--
Regards,
Tom Ogilvy



Michael E. said:
Hi Tom,

thank you very much. One question: sometimes the replacement logic is
Click to expand...
different, in this case two characeters should be replaced by one numer,
e.g.((A2=2; B1=1; C6=4; D4=3). How do I have to change your macro?
Kind regards
Michael

----- Tom Ogilvy wrote: -----

Public Sub LettersToNumbers()
Dim rCell As Range
vArr = Evaluate("{1,2;2,1;3,4;4,3}")
Application.ScreenUpdating = False
For Each rCell In Selection
With rCell
.Value = vArr(Asc(UCase(.Text)) - 64, 2)
End With
Next rCell
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


Michael E. said:
I think a simple lookup table would do, because the replacement of
Click to expand...
characters by numbers is mainly for statistic reasons. It would be of great
help for me if you could include a lookup table (A=2; B=1; C=4; D=3) in your
vba-macro.
Click to expand...
 
Back
Top