Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find / Replace / * -1
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find / Replace / * -1
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 In article , wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find / Replace / * -1
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 In article , wrote: 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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find / Replace / * -1
Then the macro will work. Format column A to retain your leading
zeros. In article , wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find / Replace / * -1
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 -----Original Message----- Then the macro will work. Format column A to retain your leading zeros. In article , wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |