Converting text to numbers from web source

[Long Nose]

(Using Windows 7 and Office 2010)

This morning I received an interesting email from mgt requesting help. The email has an excel attachment populated by copying web reports and pasting them into excel sheets.

The spreadsheet has links back to the web report and lots of hard coded numbers. However, there is a problem with two of the formats in the sales and date columns. Sales will not add together and the dates will not take a new format. I checked the web report first, but there is no excel export feature.


SALES
The sales figures are formatted with "$" & "," for thousands (English) and a "space" after the number. Hmm, it is all text! And if I can manually remove the "space" at the end of the text, then it becomes a number I can work with. Ex $4,333.00Space

The problem with this is that the number of cells needing a manual change is far too great. So, I began a series of trial-and-error fixes. First, I tried to use the =TRIM() or =CLEAN() functions, but I still could not get rid of the space at the end of the text.

Then I found an F1 help write-up with the =SUBSTITUTION( ,CHAR(160), CHAR(32)) function that does remove the "space." But, then the "$" sign itself becomes a new text causing issue.

My final solution is a combination of Trim and Substitute functions.

=TRIM(SUBSTITUTE((SUBSTITUTE((SUBSTITUTE(E4,"$","")),",","")),CHAR(160),CHAR(32)))

Where E4 held the offending text, I removed the "space" "coma" and "$". OK, now this still did not produce a number format, but if I copy/paste-special-values to next column over and then allow excel to alert me and fix the convert-to-number issue, it works. Hallelujah!


DATE
OK now for the date. There is the same "space" text issue here. The web report adds a "space" at the end and creates a text cell. Again, the easiest fix is manually F2 the cell, delete the space and hit enter to remove the offending space, but the number of records we are talking about is too large for this to be practical.
Ex. 06/15/2009"Space"

I used my new =TRIM(SUBSTITUTION) function formula to come out with this.

=TRIM(SUBSTITUTE(H3,CHAR(160),CHAR(32)))

Where H3 holds the offending text cell, this removes the "space," but I still can't format the date (06/15/09). It remains 06/15/2009 no matter what format I chose.

In the next column over I use the date function to reference my new trim date result. The resulting formula is this;

=DATE(MID(L5,7,4),MID(L5,1,2),MID(L5,4,2))

Where L5 contains the new Trim(Subs) function I am able to format the new cell. ex. 06/15/09.


I don't know if there is faster way to fix this type of problem. I did not consider writing code as I was only planning on spending a few minutes on this. Well, it took longer than I had originally thought.

Please let me know if there are other solutions out there.

Thanks!

 

[VoG]

Try the TrimAll macro here http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Select the range then run the macro.

 

[Long Nose]

Try the TrimAll macro here http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Select the range then run the macro.

Wow! Much easier.

Thanks!

 

[dlindb]

The easiest way to get rid of an unwanted space is to use the find and replace function in excel. Simply highlight the entire column of numbers with the offending spaces. Under find/replace enter a space in the find box, nothing in the replace box and choose "replace all"