Remove blank in front and back of text , number

Is this using Excel 2010 or something newer?

There is no such thing - do you mean MS365?

Please update your profile NOW to reflect this change. Thanks.

You have non-breaking spaces. Try copying and pasting this into your cell:

Please Login or Register  to view this content.

The space character was entered into the formula by holding down the ALT key and typing 0160 on the numeric keypad, then releasing the ALT key.

or:

=SUBSTITUTE(A2,CHAR(160),"")+0

which has the same effect.

or indeed (no formula dragging), delete all expected results forst:

=SUBSTITUTE(A2:A4,CHAR(160),"")+0

your formula same with my original formular. It not work.

No, it is NOT the same. I said this:

The space character was entered into the formula by holding down the ALT key and typing 0160 on the numeric keypad, then releasing the ALT key.

AND I provided a workbook to show it working.

Please look again.

sorry , not same but shown as Value.

Don't know what this means, but you could try:

Please Login or Register  to view this content.

My formula works, too - look at the workbook I provided!

CHAR(160) is the same as typing ALT+0160. Our formulae do the same thing in a different way character 160 is a non-breaking space (ASCII code 0160).

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

There are non breaking spaces before your value and there is a real space behind your value,

Please try:

Formula:

Please Login or Register  to view this content.

CHAR 160 is a non-breaking space, commonly used on the www. It can not be removed with TRIM and needs a bit of extra work. It's easier to add it, hard-coded into the formula rather than using ALT-160 which clearly didn't work easily for you.

In Excel, CHAR(160) represents a non-breaking space character.

A non-breaking space (also known as NBSP) is a special type of space character that prevents the line from breaking at that point. It is commonly used in situations where you want to keep two words or elements together on the same line without allowing a line break between them. This is useful, for example, in preventing awkward line breaks between words or keeping numerical values and their units together.

It can be common when exporting data from external systems into Excel.

Some insight about cleaning "dirty" data https://www.rondebruin.nl/win/s9/win017.htm

Marked as SOLVED.

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.