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

 

[FormR]

Re: formula or macro help.

Do you want to return everything to the right of the "+" if there is one?

If so, one formula could be:

=0+RIGHT(SUBSTITUTE(A1,"+",REPT(" ",LEN(A1))),LEN(A1))

Or you can manually do a find replace; Find: *+ Replace: nothing

Or with code:

Columns("A").Replace What:="*+", Replacement:="", LookAt:=xlPart

 

[Naxdk]

Re: formula or macro help.

The numbers are scanned into the sheet mixed, some with 30xx+1234567, 30xx1234567 and some without any prefix(1234567), end result should be the 7 digits without any prefix in all cells.


Sorry if it is not clear enough in the first post.


Hope there is a solution

 

[FormR]

Re: formula or macro help.

end result should be the 7 digits without any prefix in all cells.

If the part you want to retain is always 7 digits then you can use the RIGHT() function that has already been suggested. Although you might want to modify it slightly to coerce the result to a number:

=0+RIGHT(A1,7)