Search and replace with comma

[Munna232]

Hello All,

I want some help. Please help me.

I have comma delimited Data. When I’m converting Sting to Array, If two commas exists in between then its displaying “Zero” (because its array propriety ). Instead of that I want to display “NaN” .

Please give me any idea to replace comma to NaN. I tried with Search and Replace but it’s not giving as I expected.

[GerdW · Accepted solution]

Hi Munna,

replace all ",," appearances by ",NaN," before converting from string to array:

[Munna232]

Thanks for your reply.

Please check this snippet. Here, at end of the string, I have two commas so we will get 10,NaN,20,……. 12,NaN. But I don’t know why zero is adding to array (last element) If end of the String having only commas.

[GerdW]

Hi Munna,

you really need to explain your requirements better in the first step…

Those two last comma are converted to ",NaN,". SpreadsheetstringToArray converts this to ",NaN,Zero" because you have set the comma as separator char and this function expects a valid entry after the last comma. If you don't like this behaviour you need to delete the last comma, when it is the last char of the string!

[Munna232]

Hello GerdW,

Thanks for your reply & Sorry for the inconvenience.

As per my data file, If end of the String comma exists I need to display “NaN”. (it means 12, NaN, NaN)

[GerdW]

Hi Munna,

again a different requirement!

Replace a trailing comma with "NaN" and you're done…

Why do you add an additional trailing comma to your string? This will be no help at all!

[Munna232]

Hi GerdW,

I want to display NaN if Data is not present after the Comma.

Example:

1. 10,,12,,,30,, ----- > 10 NaN 12 NaN NaN 30 NaN NaN
2. 10,,12,,,30 ----- > 10 NaN 12 NaN NaN 30

[GerdW]

Hi Munna,

as said before: replace trailing comma by ",NaN":

[Munna232]

Hello GerdW,

Thanks you so much for your reply.

I thought, instead of replacing trailing comma by NaN adding comma before string is more better (because to many string manipulations) But, it’s taking more execution time.

[GerdW]

Hi Munna,

But, it’s taking more execution time.

When you really want to compare both approaches you should not run them in parallel. That's not the recommended way to measure execution time…