excel

[ahazaras]

I am trying to clean an excel field. I get the field from and html screen. The field is volume. It is coming across to excel with two spaces on the end. I have tried the function, find and replace but that doesn't work. I can't change the field to a number using format cell. Can anyone help. Field looks like this "5000 ". Without the quotes.

I need this field to be a number before I take it into access.

Thanks

 

[ETID]

assuming the value is in cell A1...in cell B1, try =trim("A1")

 

[ahazaras]

I have tried that and the number is still the same.

 

[gizmo1973]

Bizarre as it sounds try the "text to columns" function in Excel, this should short out your woes.
There is a "trim all" bit of VBA code knocking about on here somewhere but I can't find it at present.

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[ahazaras]

I have tried that too. the volumes vary in size so this doesn't work. It seems that the spaces are really not spaces or tabs. Here is an example of the data.

286.487  
44.863  
4000  
1100  
5500  
500  
14000  

Thanks so much for this help.
anna

 

[gizmo1973]

OK just tried your data in Excel with "text to columns" and it trimmed it down just fine.
I used delimited not fixed width, is this what you tried?

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[gizmo1973]

found it, with appropriate thanks to Skip and Dave

I always keep a copy of Dave McRitchies Trimall macro in my personal.xls. I use it any time I bring in external data:-

Just insert below into a VBA module and this should well work and is dam handy anyway

Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[ahazaras]

Yes, I tried it with tab checked off, and then with tab checked off. The field still looks the same.


anna

 

[gizmo1973]

What Excel are you using?
Where is your data coming from?

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[ahazaras]

It is excel 2000.
I get the data for a web site.
I copied the into a vb module, I think.
I ran it out of the macros. As you can guess I am new at this. I am not sure if I am doing this right.

I got an compile error. Invalid character.

 

[gizmo1973]

okkkkkk, I'm now stumped.
it sounds like a charecter that excel is not aware of probably from the website itself, I'm hopeless with VBA myself just know that the above code is normally very, very good.
How did you import the data into excel?
Was it a straight copy & paste?
Or via CSV etc?


Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[BenRowe]

What you can do is copy the char. (Go into the formula bar and select one). Then check off Other as your delimiter and paste it into that box. Also check off Treat Consecutive Delimiters as One.

 

[ahazaras]

Yes it was a straight copy and paste. I tried a paste special but the fields go all wonky.

 

[gizmo1973]

Have you tried what Ben suggested?
Even though it might not show it may work, if not that's me done for ides!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[ahazaras]

I tried what Ben suggested. It doesn't like it either. I guess I will have to delete them manually.

Thanks again.
anna

 

[gizmo1973]

Have you tried (last gasp effort) copying the the column that you got from the site into the worbook again paste special and all the above if it doesn't work!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[ahazaras]

That is what I have been working on, I didn't want to destroy the original data. What ever characters are there get copied over each time.

 

[ahazaras]

Phil and Ben Thanks. I opened up a new workbook first and them I opened up the old workbook and Ben's suggest has worked. This has saved me alot of time.

 

[gizmo1973]

I don't doubt it if it was a whole column, didn't want you to go away without an answer, that'd make us all look bad lol plus take you for ever, good luck

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[PJFry]

Sorry if you already used this solution, but I couldn't make it through the whole thread! Anywho, try this:
=LEFT(A1,LEN(A1)-2)

Of course this assumes that there are only two extra spaces at the end, which seems to be the case. After you use this, just copy.paste.special the column and there ya go. Hope this helped.

PJ