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?

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?

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?

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?

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 -

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