Remove characters

Panoos64 · Sep 23, 2019

Hi all of you, kindly require to provide me support so that to run a VBA code which should remove the 8 left characters through col. "C". Therefore that the row 1 are the headings and the code should run from "C2". I present below an extract with original data and expected result. Thank you all in advance

[TABLE="class: MsoNormalTable, width: 132"]
<tbody>[TR]
[TD="width: 43"][/TD]
[TD="width: 177"]

C

[/TD]
[/TR]
[TR]
[TD="width: 43"]

1

[/TD]
[TD="width: 177"]

Reference

[/TD]
[/TR]
[TR]
[TD="width: 43"]

2

[/TD]
[TD="width: 177"]

456123796

[/TD]
[/TR]
[TR]
[TD="width: 43"]

3

[/TD]
[TD="width: 177"]

AFT578632BN

[/TD]
[/TR]
</tbody>[/TABLE]

 [TABLE="class: MsoNormalTable, width: 132"]
<tbody>[TR]
[TD="width: 43"][/TD]
[TD="width: 177"]

C

[/TD]
[/TR]
[TR]
[TD="width: 43"]

1

[/TD]
[TD="width: 177"]

Reference

[/TD]
[/TR]
[TR]
[TD="width: 43"]

2

[/TD]
[TD="width: 177"]

6

[/TD]
[/TR]
[TR]
[TD="width: 43"]

3

[/TD]
[TD="width: 177"]

2BN

[/TD]
[/TR]
</tbody>[/TABLE]

Rick Rothstein · Sep 23, 2019

Give this macro a try...

Code:

Sub RemoveFirstEightCharacters()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .TextToColumns Range("C2"), xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
  End With
End Sub

Peter_SSs · Sep 23, 2019

Try

Rich (BB code):

Sub Remove8()
  Range("C2", Range("C" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 2))
End Sub

If you want any remainders (like the 6 in your example) as numbers if possible then change that red 2 to a 1. However, doing that note that this example would then return 2 not 02
AFT5985602

Rick Rothstein · Sep 23, 2019

Rick Rothstein said:

Give this macro a try...

Code:

Sub RemoveFirstEightCharacters()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .TextToColumns Range("C2"), xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
  End With
End Sub

Not exactly sure why I used the With..End With block structure as the object of the With statement is only referenced once...

Code:

Sub RemoveFirstEightCharacters()
  Range("C2", Cells(Rows.Count, "C").End(xlUp)).TextToColumns Range("C2"), xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub

Panoos64 · Sep 23, 2019

Thank you once again Rick. The code runs perfect. Hv a lovely day!

Peter_SSs · Sep 23, 2019

Rick Rothstein said:
Not exactly sure why I used the With..End With block structure as the object of the With statement is only referenced once...

Code:

Sub RemoveFirstEightCharacters() Range("C2", Cells(Rows.Count, "C").End(xlUp)).TextToColumns Range("C2"), xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1)) End Sub

Referencing my comments above, note that your code actually removes the first 9 characters from "AFT5985602"

Rick Rothstein · Sep 23, 2019

Rick Rothstein said:
Not exactly sure why I used the With..End With block structure as the object of the With statement is only referenced once...

Code:

Sub RemoveFirstEightCharacters() Range("C2", Cells(Rows.Count, "C").End(xlUp)).TextToColumns Range("C2"), xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1)) End Sub

Note the modification I made in Message #4 and also note Peter's code (which is essentially what I posted although he skipped the With..End With block directly), especially the note he posted after his code.

Peter_SSs · Sep 23, 2019

Panoos64 said:
Thank you once again Rick. The code runs perfect. Hv a lovely day!

Perhaps it isn't possible with your data, but note my previous comment, and also try the code with these values
AFT598562DEC
AFT598561E4

Rick Rothstein · Sep 23, 2019

Peter_SSs said:
Referencing my comments above, note that your code actually removes the first 9 characters from "AFT5985602"

I am not sure why you are saying that given the FieldInfo values in my code are the same as in your code. Plus in testing it, it removes the requested first 8 characters.

Rick Rothstein · Sep 23, 2019

Peter_SSs said:
Perhaps it isn't possible with your data, but note my previous comment, and also try the code with these values
AFT598562DEC
AFT598561E4

Ah, I see... I used Array(8, 1) instead of Array(8, 2). I would point out that I did refer the OP to your note in Message #3 when I posted Message #7 .

Remove characters

Panoos64

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Remove characters

Well-known Member

Excel Facts

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock