Trim numbers in a range of cells

[Naxdk]

I need help writing a formula or macro that can automatically trim numbers in an area of cells, ex A3 to A55, my knowledge about Excel is unfortunately not big enough so I hope some will help



The numbers come from a barcode scanner, they look like these examples:
30xx2200755, 30xx + 2200755 and 2201755, xx is entered as these numbers vary, it is 30xx and 30xx + that's needs to be removed.


It would be best with a formula to monitor the cells, but a macro that can do it with a single press can also be used


Hope there are some one who will help

 

[SteveO59L]

Re: formula or macro help.

select the column containing your data

from the home toolbar, replace, and replace xxx with

 

[Naxdk]

Re: formula or macro help.

Know the normal ways to do it, but it's too slow. Thank you anyway


It should be done with a single click or automatically, because it is in many cells with varied numbers.


Still looking for help, thanks in advance

 

[SteveO59L]

Re: formula or macro help.

something along the lines of

Columns("J:J").Select
Selection.Replace What:="xx", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Alphonse68]

Re: formula or macro help.

You can record a macro, first searching for "30xx + " and then for just "30xx", replacing both with "".
Clean it up some, if need be, and assign it to a Button or add the macro to the Ribbon or QAT.
Taking what SteveO59L wrote, you would have these two statements in the macro:

[COLOR=#333333]Range("A3:A55").Select[/COLOR]
[COLOR=#333333]Selection.Replace _
    What:=[B]"30xx + "[/B], Replacement:="", LookAt:=[B]xlPart[/B], _[/COLOR]
[COLOR=#333333]    SearchOrder:=xlByRows, MatchCase:=False, _[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]SearchFormat:=False, [/COLOR][COLOR=#333333]ReplaceFormat:=False

[/COLOR][COLOR=#333333]Range("A3:A55").Select[/COLOR]
[COLOR=#333333]Selection.Replace _
    What:=[B]"30xx"[/B], Replacement:="", LookAt:=xlPart, _[/COLOR]
[COLOR=#333333]    SearchOrder:=xlByRows, MatchCase:=False, _[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]SearchFormat:=False, [/COLOR][COLOR=#333333]ReplaceFormat:=False
[/COLOR]

HTH,
Cheers

 

[Fluff]

Re: formula or macro help.

If it's always the last 7 characters you're after try =RIGHT(A3,7) & fill down

 

[Naxdk]

Re: formula or macro help.

Thanks for the answers! I am very grateful for your help


SteveO59L and Alphonse68: It seems fine, but there is a problem when it's going to remove ex 3064 from this number 3064+2030640, the result will be 200 and not 2030640 as intended. Is it possible to add a "IF" command in your formula so that it only looks at the first 4 digits of the cells if the cell content is over 7 digits?


I have experimented a bit with "= RIGHT" but in order to work it must be combined with an "IF" formula and I have not yet learned how to use this type of formula.
The reason why it can not be used without "IF" is that the numbers entering the sheet are as described in 3 variants 30641234567, 3064+1234567 or 1234567 and should not remove anything if the number has only 7 digits.

 

[SteveO59L]

Re: formula or macro help.

try posting a larger sample of your data, and how you would like it modified

 

[Naxdk]

Re: formula or macro help.

[TABLE="width: 166"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Sample data:

3022+2754960[/TD]
[/TR]
[TR]
[TD]3042+3650270[/TD]
[/TR]
[TR]
[TD]3052+1173239[/TD]
[/TR]
[TR]
[TD]3030+4054713[/TD]
[/TR]
[TR]
[TD]3036+1871316[/TD]
[/TR]
[TR]
[TD]3018+3093202[/TD]
[/TR]
[TR]
[TD]3034+3002351[/TD]
[/TR]
[TR]
[TD]3083+1904092[/TD]
[/TR]
[TR]
[TD]3005+2824456[/TD]
[/TR]
[TR]
[TD]3003+3116726[/TD]
[/TR]
[TR]
[TD]3038+1373907[/TD]
[/TR]
[TR]
[TD]3083+1911223[/TD]
[/TR]
[TR]
[TD]3003+3118351[/TD]
[/TR]
[TR]
[TD]3049+1227819[/TD]
[/TR]
[TR]
[TD][TABLE="width: 214"]
<colgroup><col></colgroup><tbody>[TR]
[TD]30405785629[/TD]
[/TR]
[TR]
[TD]30405785035[/TD]
[/TR]
[TR]
[TD]30343041428[/TD]
[/TR]
[TR]
[TD]30292468108[/TD]
[/TR]
[TR]
[TD]30292467517[/TD]
[/TR]
[TR]
[TD]30405785248[/TD]
[/TR]
[TR]
[TD]30202767922[/TD]
[/TR]
[TR]
[TD]30202762452[/TD]
[/TR]
[TR]
[TD]30672033246[/TD]
[/TR]
[TR]
[TD]30292466930[/TD]
[/TR]
[TR]
[TD]30060214470
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]166313[/TD]
[/TR]
[TR]
[TD="align: right"]3012115[/TD]
[/TR]
[TR]
[TD="align: right"]2264945[/TD]
[/TR]
[TR]
[TD="align: right"]2800040[/TD]
[/TR]
[TR]
[TD="align: right"]3157608[/TD]
[/TR]
[TR]
[TD="align: right"]1253245[/TD]
[/TR]
[TR]
[TD="align: right"]3013283[/TD]
[/TR]
[TR]
[TD="align: right"]2866770[/TD]
[/TR]
[TR]
[TD="align: right"]1194297[/TD]
[/TR]
[TR]
[TD="align: right"]4103430[/TD]
[/TR]
[TR]
[TD="align: right"]4104971[/TD]
[/TR]
[TR]
[TD="align: right"]3012108[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

Re: formula or macro help.

so for 3022+2754960 how would you like this modified?