Macro to remove extra digits 1

[cdulong]

I am looking for a way to remove extra digit from some of our account code. Here is how the accounting software outputs the information

4101-002
4505-060
4501-100

What I need it a macro or script to remove the 0's on the right of the - up to the first digit that is not a 0 giving the following.

4101-2
4505-60
4501-100

Any help would be great

Thanks!

 

[HarleyQuinn]

Not sure what flavour of VBA you're using but a function for this using regular expressions could look like:

Public Function RemoveZeros(strInput As String) As String
Dim re As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    .Pattern = "(-)0{1,3}"
    RemoveZeros = .Replace(strInput, "$1")

End With

Set re = Nothing

End Function

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[cdulong]

Thanks for the help I am using visual basics for excel.

If you could re write it so that I can use it there that would be great.

THanks,

 

[HarleyQuinn]

From you're response I'm going to assume you didn't even try it in Excel then...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[cdulong]

I am fairly new to these types of scripts and I cannot get it to run correctly.

What I am looking for is to select cells A1:A1000 and click a button that remove the excess 0's.

Hope you can help with this thanks

 

[taupirho]

You might not have to use VBA, the following excel formula will do want you want - assume your string is in cell E61.

=LEFT(E61,SEARCH("-",E61)-1)&"-"&INT(MID(E61,SEARCH("-",E61)+1,LEN(E61)-SEARCH("-",E61)))


In order to understand recursion, you must first understand recursion.

 

[HarleyQuinn]

Yes, it's suprisingly alot easier than you'd think. Put a command button on your sheet and in the code behind it use:

Private Sub CommandButton1_Click()

Dim c

For Each c In Application.Selection
    c.Value = RemoveZeros(c.Value)
Next c
End Sub

You'll also have to include the RemoveZeros function I've already posted.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[HarleyQuinn]

taupirho - Yes, that would do it too.

Our solutions handle 4501-000 differently but as the OP has expressed no requirement in that respect I doubt it matters at this point.

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[cdulong]

Worked just as planned!!!

THanks!!

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Gavona]

For any future viewer another (non-vba solution would be to use edit, replace
Replace -0 with -
Run it a few times until no more occurences are found (after first pass 4101-002 will have become 4101-02, after second pass it will be 4101-2 and third pass with your sample data would not find -0

Gavin

 

[MarkWalsh]

But, if you used Harley's example of 4501-000, then you would end up with a result of 4501- if you ran it 3 times.

 

[Gavona]

Which is surely what the op asked for/specified?

remove the 0's on the right of the - up to the first digit that is not a 0

If a maximum of 2 zeros were to be removed then clearly you would loop through the edit replace just twice.


Gavin

 

[fumei]

Which is surely what the op asked for/specified?"

Well...not really. Although, sort of.

"remove the 0's on the right of the - up to the first digit that is not a 0"

As HarleyQuinn pointed out: "but as the OP has expressed no requirement in that respect I doubt it matters at this point."

I suspect it does (or may) matter, but there it is...no requirement was expressed. The OP did not mention anything regarding the situation if there is no first digit not a 0.

Perhaps that situation will never occur. Who knows?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry