Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last 6
I'm trying to recall the vb syntax to perform the above function,
which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last 6
Right, Left or Mid functions, depending on how your state them.
"S Himmelrich" wrote: I'm trying to recall the vb syntax to perform the above function, which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last 6
Can you give me an example of the statement that I'd put in VB?
On Dec 6, 4:52 pm, JLGWhiz wrote: Right, Left or Mid functions, depending on how your state them. "S Himmelrich" wrote: I'm trying to recall the vb syntax to perform the above function, which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last
3180015155 002075
and replace the current data with the last 6 characters like this: 002075 Assume the above data is in cell B2 of the ActiveSheet. myVar = Right(Range("B2").Value, 6) 'myVar returns 002075 Or myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075 There are more details available in the VBA help files by pressing Alt+F11 and typing [Right Function] or [Mid Function] in the Help search box, then select the appropriate one from the menu that is displayed. "S Himmelrich" wrote: Can you give me an example of the statement that I'd put in VB? On Dec 6, 4:52 pm, JLGWhiz wrote: Right, Left or Mid functions, depending on how your state them. "S Himmelrich" wrote: I'm trying to recall the vb syntax to perform the above function, which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last
Here is what I've come up with:
' Convert account column Columns("B:B").Select Selection.Value = Right(Range("B2").Value, 6) 'myVar returns 002075 It doesn't work, it duplicates B2 for all cells with last six characters...how to make it cycle through all records? On Dec 6, 9:11 pm, JLGWhiz wrote: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 Assume the above data is in cell B2 of the ActiveSheet. myVar =Right(Range("B2").Value, 6) 'myVar returns 002075 Or myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075 There are more details available in the VBA help files by pressing Alt+F11 and typing [RightFunction] or [MidFunction] in the Help search box, then select the appropriate one from the menu that is displayed. "S Himmelrich" wrote: Can you give me an example of the statement that I'd put in VB? On Dec 6, 4:52 pm, JLGWhiz wrote: Right, Left or Mid functions, depending on how your state them. "S Himmelrich" wrote: I'm trying to recall the vb syntax to perform the abovefunction, which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert column data to trim all characters and leave the last
On Dec 12, 8:40 pm, S Himmelrich wrote:
Here is what I've come up with: ' Convert account column Columns("B:B").Select Selection.Value = Right(Range("B2").Value, 6) 'myVar returns 002075 It doesn't work, it duplicates B2 for all cells with last six characters...how to make it cycle through all records? On Dec 6, 9:11 pm, JLGWhiz wrote: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 Assume the above data is in cell B2 of the ActiveSheet. myVar =Right(Range("B2").Value, 6) 'myVar returns 002075 Or myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075 There are more details available in the VBA help files by pressing Alt+F11 and typing [RightFunction] or [MidFunction] in the Help search box, then select the appropriate one from the menu that is displayed. "S Himmelrich" wrote: Can you give me an example of the statement that I'd put in VB? On Dec 6, 4:52 pm, JLGWhiz wrote: Right, Left or Mid functions, depending on how your state them. "S Himmelrich" wrote: I'm trying to recall the vb syntax to perform the abovefunction, which will evaluate each row in a column that will have this type of data: 3180015155 002075 and replace the current data with the last 6 characters like this: 002075 recommendations?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Sub convert() If ActiveCell.Value < 0 Then Do Until ActiveCell.Value = "" ActiveCell.Copy ActiveCell.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.CutCopyMode = False ActiveCell.Value = Right(ActiveCell, 6) ActiveCell.Offset(1, -1).Select Loop End If End Sub This is what i understand from your text. Place the cursor on the first value and run this code. It will copy the current value into other cell and trim for the last six right digits. and carry on working till the active cell is "" or empty. Hope this will help........ Thanks, AA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim out last seven characters from a string... | Excel Discussion (Misc queries) | |||
Trim characters | Excel Worksheet Functions | |||
How do you trim characters? | Excel Worksheet Functions | |||
Trim the last two characters? | Excel Programming | |||
Trim characters | Excel Programming |