Space after number as text is removed by Find and Replace along with leading zeroes.

Hello.

I have an excel file, which is used for accounting of some equipment.
We have a barcode scanner, which scans s/n's and enters them in excel.
Those serial numbers are of different types: 001AC310B838, 3105189007, 00532505231, 20078700024F8A030413, HCC8CASOJIC0FCNSN0016105.

We have problem with serial numbers such as 00532505231.
Cell that scaner is entering those serials into is set to Text format.

For some reason when scanning this type of serials it adds extra space after last character, and because of that some formulas do not work correctly.

If I try to Find and Replace space with nothing - it does remove space, but also removes first two 0.
So, after scanning I have cell with value "00532505231 ", I replace space with nothing and I get "532505231". Why is this happening?
It still is a Text format field after replace.

Ideally it would be perfect to find the solution for scanner (or is it excel) to not add that extra space at the end, and also to fix all those already scanned serials by removing extra space.

Any ideas?

try this
TRIM (your_data)

Originally Posted by dwint

try this
TRIM (your_data)

Yeah, that seems to be an easy way to fix existing spaces.
But this is not a solution on how to fix the scaner or excel adding that space in the first place.

I thought of workaround - instead of scanning s/n to column B for example, scan it to column C, and in column B use this TRIM function pointed to according C column cell...

But it's not very convenient.
Would be cool to somehow do it on the air. So when scanning - it would trim it in the same cell right away.

We used barcode scanner in Notepad and there is no extra space.

Which means, that it is Excel, who adds this space.

Does anyone know why might it do this?

you can enter one of those serial numbers in Excel and then download a barcode add-in for Excel to generate a barcode according to the serial number. Now you can chech out whether the serial number still has the same problem in the Excel document. I am not sure if it works. If you want to know where to download free trial barcode add-in for Excel, I can reconmmend you a website.

Originally Posted by SergSlim

We used barcode scanner in Notepad and there is no extra space.

Which means, that it is Excel, who adds this space.

Does anyone know why might it do this?

you can enter one of those serial numbers in Excel and then download a barcode add-in for Excel to generate a barcode according to the serial number. Now you can chech out whether the serial number still has the same problem in the Excel document. I am not sure if it works. If you want to know where to download free trial barcode add-in for Excel, I can reconmmend you a website.