Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
Hello all,
I have a request from a Business Unit and I am baffled. We will be getting an excel spreadsheet each day and the amounts in a particular column will change as will the number of rows. i need to replace the last character in the amount with a particular alpha character. example: 20.50 = 20.5} 65.01 = 65.0J it only changes the last penny. if anyone could help on this, i would greatly appreciate it. i am not savvy in this. thanks, tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
what criteria do you use for determine what the last character will be replaced by?? eg. if the last charcacter is a 1 do your replace it with a "A" if the last charcacter is a 2 do your replace it with a "B" savvy in this. thanks, tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
Assuming the character is not a number and that it is actually stored in the
cell (making the whole cell a text string) rather than produced through formatting. select the column and do Edit=Replace What: } With: J -- Regards, Tom Ogilvy "Tony" wrote: Hello all, I have a request from a Business Unit and I am baffled. We will be getting an excel spreadsheet each day and the amounts in a particular column will change as will the number of rows. i need to replace the last character in the amount with a particular alpha character. example: 20.50 = 20.5} 65.01 = 65.0J it only changes the last penny. if anyone could help on this, i would greatly appreciate it. i am not savvy in this. thanks, tony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
stevebriz wrote: what criteria do you use for determine what the last character will be replaced by?? eg. if the last charcacter is a 1 do your replace it with a "A" if the last charcacter is a 2 do your replace it with a "B" savvy in this. thanks, tony thanks for the reply. the citeria will always be: 1=J 2=K 3=L 4=M 5=N 6=O 7=P 8=Q 9=R 0=} i really appreciate it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
this would be to time consuming to do daily.
Tom Ogilvy wrote: Assuming the character is not a number and that it is actually stored in the cell (making the whole cell a text string) rather than produced through formatting. select the column and do Edit=Replace What: } With: J -- Regards, Tom Ogilvy "Tony" wrote: Hello all, I have a request from a Business Unit and I am baffled. We will be getting an excel spreadsheet each day and the amounts in a particular column will change as will the number of rows. i need to replace the last character in the amount with a particular alpha character. example: 20.50 = 20.5} 65.01 = 65.0J it only changes the last penny. if anyone could help on this, i would greatly appreciate it. i am not savvy in this. thanks, tony |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
Tony wrote: Hello all, I have a request from a Business Unit and I am baffled. We will be getting an excel spreadsheet each day and the amounts in a particular column will change as will the number of rows. i need to replace the last character in the amount with a particular alpha character. example: 20.50 = 20.5} 65.01 = 65.0J it only changes the last penny. if anyone could help on this, i would greatly appreciate it. i am not savvy in this. thanks, tony All, I figured it out: Sub UpdateValues() Dim rng As Range, cell As Range, s As String Dim i As Long Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) Dim oldVal, newVal, o As String For Each cell In rng s = Right(cell.Value, 1) o = Len(cell.Value) oldVal = Left(cell.Value, o - 1) Select Case s Case 1 newVal = Replace(s, "1", "J") cell.Value = oldVal + newVal Case 2 newVal = Replace(s, "2", "K") cell.Value = oldVal + newVal Case 3 newVal = Replace(s, "3", "L") cell.Value = oldVal + newVal Case 4 newVal = Replace(s, "4", "M") cell.Value = oldVal + newVal Case 5 newVal = Replace(s, "5", "N") cell.Value = oldVal + newVal Case 6 newVal = Replace(s, "6", "O") cell.Value = oldVal + newVal Case 7 newVal = Replace(s, "7", "P") cell.Value = oldVal + newVal Case 8 newVal = Replace(s, "8", "Q") cell.Value = oldVal + newVal Case 9 newVal = Replace(s, "9", "R") cell.Value = oldVal + newVal Case 0 newVal = Replace(s, "0", "}") cell.Value = oldVal + newVal End Select Next End Sub thanks all for help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing dynamic currency amounts with a character
hi tony
I just did something similar... has another check.. 1/ accounts for if the number that end like 0.20 has only 0.2 showing... Dim i As Integer ' for rows Dim j As Integer ' for columns Sheet1.Activate Sheet1.Cells(1, 1).Select ' lets assume the number you want to change is is rows 1-20 in column 1(A)on sheet 1 Sheet1.Activate ' show sheet 1 Sheet1.Cells(1, 1).Select ' select sheet 1 A1 j = 1 ' 1 = column A For i = 1 To 20 ' rows 1 to 20 If Cells(i, j).Value = vbNullString Then ' check the cell is not empty Else If Not IsNumeric(Right$(Cells(i, j).Value, 1)) Then ' checks last char is a number Else ' this to cover if the zeros are dropped after the decimal point If Mid(Cells(i, j).Value, ((Len(Cells(i, j).Value) - 2)), 1) < "." Then Cells(i, j).Value = Cells(i, j).Value & "}" Else ' truncates the string and adds the correct character from function Cells(i, j).Value = Left$(Cells(i, j).Value, Len(Cells(i, j).Value) - 1) & newlastchar(Right$(Cells(i, j).Value, 1)) End If End If End If Next i End Sub Function newlastchar(ln As Integer) Select Case ln Case 1 newlastchar = "J" Case 2 newlastchar = "K" Case 3 newlastchar = "L" Case 4 newlastchar = "M" Case 5 newlastchar = "N" Case 6 newlastchar = "O" Case 7 newlastchar = "P" Case 8 newlastchar = "Q" Case 9 newlastchar = "R" Case 0 newlastchar = "}" End Select End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding currency amounts | Excel Discussion (Misc queries) | |||
Replacing multiple instances of character | Excel Worksheet Functions | |||
Formatting monetary amounts with currency indicator on extreme left and amount right ? | Excel Discussion (Misc queries) | |||
Replacing spaces in text, with another character | Excel Worksheet Functions | |||
Currency Amounts | Excel Programming |