can't get rid of leading space in cell

[shammer]

I have tried four things but for some reason they do not work. I have cut and pasted a column of numbers into excel, unfortunately they have a space in the field before the number begins.

I tried to convert the column by using =trim(cell) command. Did not work.

I tried to use the Text to Columns function but could not get it to work.

I used the Replace function by Finding " " and replace with "". That did not work.

Tried a version of someone's macro here, but only ended up deleting entire number in cell when it recognized a blank space in the cell.

I am thoroughly confused. Can someone provide some help please?

 

[Rocky E]

Shammer,
I am an old programmer, but new to the Excel VBA world. I have a very similar problem with B2B data from the Web that has formatting characters included along with the data. My point is I would suggest you reverse the test. Testing for 160 may work today, but down the road you will discover a 161 or something else weird. Test for the characters you want a toss the rest. I have included my macro for decimal numbers. Maybe you can adapt this for your problem.

If the cruddy data is in A1 out to the right in an unused column I place =trim_dec(A1)+0 the +0 turns a clean string into a number.

Public Function trim_dec(strIn As String) As String
Dim strOut As String
Dim lngInPtr As Long

For lngInPtr = 1 To Len(strIn)
If "." = Mid$(strIn, lngInPtr, 1) Then
strOut = strOut & "."
Else
If Mid$(strIn, lngInPtr, 1) >= "0" And Mid$(strIn, lngInPtr, 1)<= "9" Then
strOut = strOut & Mid$(strIn, lngInPtr, 1)
End If
End If
Next
trim_dec = strOut
End Function

This is a crude, but very effective filter.
Of course if you improve it I would love to see you version. (or anyones)

HTH
Rocky...
This message was edited by Rocky E on 2002-04-10 18:46

 

[shammer]

Thanks to all for their help. The version that worked for me was the second one on Russell's list. This will be a tremendous time saver for me. Hope to return the favor to someone as my knowledge increases.

 

[husnain5255]

You are the best.. thanks alot..

 

[husnain5255]

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

should clean the culprit. If the entries are supposed to be numbers, use:

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

Copy the cells of this formula and execute Edit|Paste >Values, and delete the original column.

Aladin

You are the best.. thanks Aladin