Forum Discussion
Number format
As a Realtor I have, over the years, downloaded bulk MLS data for offline processing in Excel for the benefit of my buyer and seller clients. There was never a problem until the MLS service (Garden State Multiple Listing Service in NJ) overhauled its front end. In the process the back-end data appears to have been changed, as well. I have attached a small sample of the data as it exists now. Istext confirms that it is, indeed, text, but I can't find a way to convert it to useable numbers.
Thanks for any help you can provided.
12 Replies
Since your data format appears so consistent, you could also use the MID function to 'top and tail' the text. I use Names to reference data so I would define 'rentPrice' to refer to
= VALUE( MID( input, 2, LEN(input)-2 ) )
That tucks the boring junk out of sight and the formula
= SUM( rentPrice )
is clear in terms of its business intent.
- In addition to the solution provided:
1. Convert the values to Excel Table using CTRL + T
2. In the Data tab, select From Table/Range
The data will be loaded into the Power Query editor and Power Query will automatically convert the text values to numbers! So easy!
Then you can select Close and Load to load the result back to Excel.Thank you. I tried it and worked like a charm. I need to learn more about the Power Query Editor so I can understand what's going on and whether it will work with how my data is layed out.
- With Power Query, you can do so much effortlessly!
- Your sample file does not appear to contain data which has been downloaded from anywhere? To convert the data to numbers, simply do a search and replace, replacing the $ character with nothing. Also, each number appears to have a character behind it which needs to be removed. It is character 160 (non-breaking space character) so another S&R operation is needed for that.
Still no luck. Tried substitute...SUBSTITUTE(D2,CHAR(160),"")...and clean and the text was still unworkable.
- You were close. Substitute produces text so the trick is to multiply the result by 1, see attached.
JKPieterse Thank you. I'll try it. How do you conduct a search and replace using a character number?
