Removing White Space in a Cell

[kerrymaid]

Hi all,

I have data generated from a simulator package I use (attached in a workbook). It gives me the cdf for 4 different schemes (entitled GHLS1 non perf, GHLS 09.5, GHLS 0.25 and MBLS). From the simulator software I know what the CDF should look like (also attached) but I need to graph the CDF in excel. As you'll see from the attached workbook, when I directly graph this with a scatter plot, it doesn't plot the data correctly. I suspect I must manipulate the dataset in some way.

Any clues?

Thanks in advance.

cdf.gif

[XOR LX]

Hi,

It is difficult to answer your question as the data for the chart in the attached is contained in different workbooks; as such, it is not possible to analyse this data and to therefore potentiallly rule it out as a cause.

Regards

[kerrymaid]

Hi XOR LX,

Apologies. I've reattached the workbook with the links to the data updated.

[XOR LX]

Hi again,

The reason that your graphs are not plotting as you wish is that you have an enormous amount of extra spacing, non-printable characters, etc. lurking in your data, and hence Excel cannot recognise it as numerical.

This is the reason that your x-axis is defaulting to a maximum value of 267, since that it is the number of data points in the largest of your 4 sets (mbls). In effect, Excel is by defaut replacing your true x-axis values with 1, 2, 3, ..., 267.

I'm not sure of the exact export set-up that you have from this simulator package of yours, but you need to make amendments, either by defining the output format prior to exporting (ideal), or, if this is not possible, then, once exported to Excel, performing a comprehensive clean-up operation on the data (removing extra spacing and non-printable characters, making sure data is formatted as numerical, etc., etc.)

I did this for one of your four datasets (unfortunately I'm not willing to do it all for you!) and the plot was as you would expect (i.e. identical to that in your picture).

Hope that helps and best of luck!

Regards

[jeffreybrown]

@kerrymaid,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...

[kerrymaid]

Hi XOR LX,

Thanks for the reply - makes sense!

Two quick questions on the cleaning:

I manually removed the non printable 'E' character and changed the format of all the cells to number. I must remove the spacing before each number.

1. I tried to use the trim() function as well as find replace (find ' ', a space character and leaving replace empty) but neither have worked. What method did you use to remove the white space?
2. Is there anything else I've omitted that you did in order to get a clean dataset?

Thanks again.

[FDibbins]

Kerrymaid....

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc

[kerrymaid]

Hi everyone,

Sorry I didn't intentionally ignore any request. I didn't see the request when I sent the reply. I updated the thread title a moment ago so hopefully it's amended.
Appreciate the help so far

[XOR LX]

Hi,

Ok - now that that's sorted, I'm pretty sure we can get rid of all this in one go, so try this:

Open the Find and Replace box. In the Find what: box, hold down the ALT key and type 0160 on the numeric keypad (not the number keys above the letters). Leave the Replace with: box blank. Click on Replace All.

With any luck that should clean everything up (your graphs might even be correct after that).

Regards

[Marcol]

Another way to get CHAR(160) is to type this in the formila bar, and press f9 not Enter.

Please Login or Register  to view this content.

Copy the formula bar result to Find/Replace.

[XOR LX]

@Marcol

Nice! Never thought of doing it that way.

[kerrymaid]

@ XOR LX, Marcol - Thanks for all your help. The second solution solved it and I have now have the correct CDF

[XOR LX]

Glad we could help!

[Marcol]

It's also handy way to check part of a formula inline, just remember to press undo after you have checked the result.

e.g.
Put some numbers in column A

Then in B2

Please Login or Register  to view this content.

Select the MATCH() in the formula bar press f9 and the formula will read with the last row number inline.

[XOR LX]

@Marcol

Perhaps you should have a go at coming up with a more technical explanation than I did for this phenomenon, then?

http://www.excelforum.com/excel-form...ng-result.html

Regards