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?

 

[zahidzmk]

i did that as well,

when i copy & paste as Vulvae same data form workbook A to B then is works fine.. e.g A1 to A1

 

[ryan0521]

Try ctrl + "a" to workbook B then delete it all before you start.

 

[zahidzmk]

is it mean delete the whole data from B & paste again?

 

[nazmul_muneer]

Have you copied text from Pdf or email?

hello,

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

anyone plz help me here?

 

[Ashu1727]

Thanks mite
Can you please temme why you have used trim function here?

 

[arebarchik]

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

^This guy is on the right track.

Both trim and my first find/replace attempt did not work, but here's what I did that worked:

Go to one of the cells you are trying to remove the space(s) from and highlight the "text" (the space) you want to remove. Copy+Paste that selection into the find field of the find/replace window, do not put anything in the replace field, and hit replace all.

 

[kanhaiyaiway1]

=VALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),MID(A2,1,1),""))
This formula will remove all spaces in your numeric value
100% workable..!!!

 

[mashfisarwar]

this worked...thanks very much man :D

 

[jtakw]

Hi,

Assuming data in A1,

If it's a Real Space, then the Value Must be Text, as Excel will automatically drop spaces for numbers entered, in that case, Real Space(s) with a Text Number, you just need this:

=A1+0

Otherwise, it Must be a Non-breaking Space, CHAR(160), usually happens when data is imported or downloaded, then just use this:

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