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.

Sample data: 3022+2754960

3042+3650270

3052+1173239

3030+4054713

3036+1871316

3018+3093202

3034+3002351

3083+1904092

3005+2824456

3003+3116726

3038+1373907

3083+1911223

3003+3118351

3049+1227819

30405785629 30405785035 30343041428 30292468108 30292467517 30405785248 30202767922 30202762452 30672033246 30292466930 30060214470 166313 3012115 2264945 2800040 3157608 1253245 3013283 2866770 1194297 4103430 4104971 3012108 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[SteveO59L]

Re: formula or macro help.

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