Convert column data to trim all characters and leave the last 6

[S Himmelrich]

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?

 

[JLGWhiz]

Right, Left or Mid functions, depending on how your state them.

 

[S Himmelrich]

Can you give me an example of the statement that I'd put in VB?

 

[JLGWhiz]

3180015155 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]

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?

3180015155 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.

Can you give me an example of the statement that I'd put in VB?

- Show quoted text -

 

[Abi]

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?

3180015155 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.

- 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