Find / Replace / * -1

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

Guest

I have a column with numbers. The numbers end in alpha
(A, B, C...). I need to search the column and find 'A'
replace with the number 1 and then multiply by -1. Do
this for all the A's, then search for 'B' and replace
with the number 2 and multiply by -1 and so forth.

Any thoughts on how to do this?
 
If each entry only has one letter, then this may work for you:

Public Sub ReplaceAlphas()
Dim rCell As Range
Dim sRightChar As String
Application.EnableEvents = False
On Error Resume Next
For Each rCell In Columns(1).Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With rCell
sRightChar = Right(.Text, 1)
If sRightChar Like "[A-Z]" Then _
.Value = -(Left(.Text, Len(.Text) - 1) & _
Asc(sRightChar) - 64)
End With
Next rCell
On Error GoTo 0
End Sub
 
Yes each entry only has one letter, and it is at the end
of the data, for example column A has:

0000118800A
0000086780B
0000085170C
0000048567A
0000045830B
0000036000C
0000027140A
0000026170B
0000024280C

I need to find and replace ALL the 'A' with 1 and
multiply by -1, then find and replace ALL the 'B' with 2
and multiply by -1, then find and replace ALL the 'C'
with 3 and multiply by -1. So my results would be

-00001188001
-00000867802
-00000851703
-00000485671
-00000458302
-00000360003
-00000271401
-00000261702
-00000242803

-----Original Message-----
If each entry only has one letter, then this may work for you:

Public Sub ReplaceAlphas()
Dim rCell As Range
Dim sRightChar As String
Application.EnableEvents = False
On Error Resume Next
For Each rCell In Columns(1).Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With rCell
sRightChar = Right(.Text, 1)
If sRightChar Like "[A-Z]" Then _
.Value = -(Left(.Text, Len(.Text) - 1) & _
Asc(sRightChar) - 64)
End With
Next rCell
On Error GoTo 0
End Sub





I have a column with numbers. The numbers end in alpha
(A, B, C...). I need to search the column and find 'A'
replace with the number 1 and then multiply by -1. Do
this for all the A's, then search for 'B' and replace
with the number 2 and multiply by -1 and so forth.

Any thoughts on how to do this?
Click to expand...
.
Click to expand...
 
A formula to do all that is
="-"&LEFT(A1,LEN(A1)-1)&(CODE(RIGHT(A1,1))-CODE("A")+1)
The result is treated as a string so you don't need to
format the cells to avoid loss of leading zeroes.

Kevin Beckham
 
Back
Top