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]

Panoos64 · Sep 23, 2019

Hi Peter and thank you so much for the comment you done. I did not think that i already i have such cases which are ending into 0000000. Therefore that i need to keep such reference. The code runs but it appears it like just 0 (one zero) I would much appreciate if you could adjust it. Many thanks also for your time spent for my project. Hv a great day!

Rick Rothstein · Sep 23, 2019

Panoos64 said:
Hi Peter and thank you so much for the comment you done. I did not think that i already i have such cases which are ending into 0000000. Therefore that i need to keep such reference. The code runs but it appears it like just 0 (one zero) I would much appreciate if you could adjust it. Many thanks also for your time spent for my project. Hv a great day!

See Message #10 and make that adjustment in my code... or just use the equivalent version Peter posted in Message #3 .

Panoos64 · Sep 23, 2019

Hi again Rick, I have the same issue as i wrote to Peter. Apologies that i did not mention on my first post. However i have references which are ending to 7 and more 0. Please can you modify the code so that to appears it on right way. Many thanks once again for your continuance support!

Peter_SSs · Sep 23, 2019

Panoos64 said:
Hi Peter and thank you so much for the comment you done. I did not think that i already i have such cases which are ending into 0000000.

My comments were not specifically about cases ending with a string of zeros but rather cases where there was a zero in position nine and that zero was followed only by other digits (0-9)

Panoos64 said:
Therefore that i need to keep such reference. The code runs but it appears it like just 0 (one zero)

My code does not return just one 0, it returns whatever characters are after the 8th character of your original data.

Original data

Excel 2016

C

1

Reference

2

456123796

3

AFT578632BN

4

AFT5985602

5

AFT598562DEC

6

AFT598561E4

7

X123456700000

TTC

After my code.

Excel 2016

C

1

Reference

2

6

3

2BN

4

02

5

2DEC

6

1E4

7

00000

TTC

Panoos64 · Sep 23, 2019

Hi Peter and Rick. I still get the below result. Apologies that I bother so much for my project. In case that is complicate, do not worry about. I will try to find the right formula. I express both of you my thanks for your kind support!

Original data

Excel 2016

	C
Reference
456123796
AFT578632BN
AFT5985602
AFT598562DEC
AFT598561E4
X123456700000

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

TTC

After run the code.

Excel 2016

	C
Reference
6
2BN

02-DEC

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.00E+04
[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0
[/TD]

</tbody>

TTC

Peter_SSs · Sep 23, 2019

Panoos64 said:
Hi Peter and Rick. I still get the below result. Apologies that I bother so much for my project. In case that is complicate, do not worry about. I will try to find the right formula. I express both of you my thanks for your kind support!

After run the code.

Excel 2016

C
Reference
6
2BN
02-DEC

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.00E+04
[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0
[/TD]

</tbody>
TTC

Are you sure that you are using this code - with a 2 at the end?

Peter_SSs said:

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

Panoos64 · Sep 23, 2019

Apologies Peter, it was my fault. Is perfect using the above. I am sorry once again that i bother you so much for such issue. I express my thanks to you. Have a great, lovely day!!

Peter_SSs · Sep 23, 2019

Panoos64 said:
Apologies Peter, it was my fault. Is perfect using the above. I am sorry once again that i bother you so much for such issue. I express my thanks to you. Have a great, lovely day!!

OK, glad we got there in the end.
Highlights the importance of attention to detail.

Remove characters

Panoos64

Well-known Member

Panoos64

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Remove characters

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

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