Remove space(s) before a numerical value within a cell

[DMP3]

Trying to remove the space in front of the number '1' in cell A2.

So value in cell A2 is = " 1"

TRIM function does not work for removing the space before the 1.

Any ideas please?

 

[Jon von der Heyden]

Find and Replace (Ctl+H)

Select column A
Find: (enter a space)
Replace: (leave blank)
Hit Replace All

 

[wigi]

TRIM function does not work for removing the space before the 1.

It should work in theory.

Can you show us how you use the formula? In what cell?

Is it really a space in front of the 1?

 

[mikerickson]

TRIM works for me.
What does =CODE(A2) return?

 

[T. Valko]

Trying to remove the space in front of the number '1' in cell A2.

So value in cell A2 is = " 1"

TRIM function does not work for removing the space before the 1.

Any ideas please?

The offending character may not be a standard char 32 space.

What result do you get with this formula:

=CODE(A2)

That will return the character code of the offending character. Once we know that we'll have a better idea of how to kill it!

 

[mikerickson]

If all else fails, =MID(A2, 2, 255)

 

[DMP3]

Thank you everybody but it looks like the issue is because my colleague has OLD exel and I have NEW excel... he has had many issues with his excel and some formulas not working properly.

Thank you all again.

 

[nazmul_muneer]

Use the formula
=VALUE(SUBSTITUTE(TRIM(A2),CHAR(160),""))

 

[zahidzmk]

hello,

Trim worked in Workbook A but not working in the workbook B on same DATA??

anyone plz help me here?

 

[ryan0521]

Maybe your format is Text, change it to General.