Removing Trailing Spaces/Adding Leading 0's

[bigleroy]

I have been copying reports from a web page. When I paste into excel, every field ends with a space. (ex "19.99 &quot This is preventing me from formatting the cells as numbers or currency. I have tried Ctrl + H and it will not recognize the end space. I have also tried the trim function, which does not remove the space.

The best solution I have so far is to create a reference to the column using this String Function:
=VALUE((MID(F3,1,5)))

However, this only works for fixed length fields. Any suggestions?

Thanks,
Leroy

 

[acron]

In an unused cell on the worksheet enter the numeric value 1. Format it as you wish the cells you are having problems with to be formatted, Currncy in this case.

The select the single cell and copy it. Then select th eproblematic cells, go to Edit, PasteSpecial and select Multiply. This action should convert the cells to numeric value with the formatting you applied to the single copied cell.

A.C.

 

[bigleroy]

I tried what you suggested, but the result was "$1.00" no matter what "amount" was in the problematic column. I believe this is because the problematic column is not converting accurately from a text to a number. Is there a way to pad zeros to the left of a decimal in a text field?

Thanks for your help,
Leroy

 

[acron]

Leroy that method has never failed me. Try this formula

=REPT(0,10-LEN(F3))&F3

which takes F3 and adds leading zeros to bring the length of the string to 10.

Does =VALUE(F3) not work either ?

A.C.

 

[bigleroy]

Thanks Acron,

I combined your function with mine and it worked? You've made me the office hero.

=VALUE((MID(REPT(0, 10-LEN(I22))&I22, 1, 9)))
Then I can format the cells however I like.

Thanks again,

Leroy

 

[xlbo]

Just as an addition - if the cells only have the 1 space at the end then =VALUE(MID(F3,1,len(F3)-1))
should also work (and be a bit shorter) - no need for leading zeros as far as I can see......
HTH
Geoff

 

[WildWest]

Do you guys know how to trim leading zeros?

For example, I have "0003457823"
and I only want "3457823"

Thanks in advance!

 

[mbarron]

WildWest,
Use the "PasteSpecial" - Multiply method that acron details it the first response.

Mike

 

[KenWright]

Anybody that pulls data in from a webpage should absolutely arm themselves with a copy of Dave McRitchie's (Microsoft MVP - Excel) superb 'Trimall' macro. It will clear all that garbage out in a nanosecond, and much more.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Approx 1/3 of the way down the page.

Regards
Ken................