# REPLACING SPACE VALUES WITH MISSING VALUE

**URL:** <https://forum.knime.com/t/replacing-space-values-with-missing-value/71268>\
**Category:** KNIME Analytics Platform\
**Created:** [July 31, 2023, 1:47pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268 "2023-07-31T13:47:28Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ajisafeadeoye](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@ajisafeadeoye](https://forum.knime.com/u/ajisafeadeoye)\
**Post date:** [July 31, 2023, 1:47pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/1 "2023-07-31T13:47:29Z")

</div>

Hi KNIMERS, please i need to replace the cells filled with space with missing values(?)

i tried String Manipulation Node(strip) to remove space and repalce with missing Value but was futile

Please i need a way out with it

 ![sample 2](https://forum-cdn.knime.com/uploads/default/original/3X/7/d/7d25235388ff43d65f5ae54abb37ece9038b00e3.jpeg)

Thanks

---

<div class="post-metadata">

**Author:** ![denisfi](https://forum-cdn.knime.com/user_avatar/forum.knime.com/denisfi/32/83308_2.png) [@denisfi](https://forum.knime.com/u/denisfi)\
**Post date:** [July 31, 2023, 2:02pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/2 "2023-07-31T14:02:46Z")

</div>

Hi,

As I know, you cant set a missing value, but if you have space, you can convert it to empty

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/d/2/d2a06f5e195e30f619e06c8aa96239fa30bad0d8.png)

It can help you?

BR,

Denis

---

<div class="post-metadata">

**Author:** ![HeatherPikairos](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/a88e4f/32.png) [@HeatherPikairos](https://forum.knime.com/u/HeatherPikairos)\
**Post date:** [July 31, 2023, 2:06pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/3 "2023-07-31T14:06:01Z")

</div>

Hi @ajisafeadeoye

Edit: In order to not cause confusion with my answer, I will add this little explanation. My solution only applies if the cells are blank rather than containing spaces. It can be used if you want them to be converted to missing values (eg. have the red ? in the cell)

There may be other methods, but the one I know of is to use the -Column Auto Type Cast- node:

> **[Column Auto Type Cast](https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.preproc.colautotypecast.ColumnAutoTypeCasterNodeFactory)**
>
> This node determines the most specific type in the configured string columns and changes the column types accordingly. The type order is to first check if the …

Change the missing value pattern to “Empty”:

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/1/21437afc60aaff212b9829a8bc7f32266d6e71ae.png)

Hope that helps,  
Thanks  
Heather

---

<div class="post-metadata">

**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\
**Post date:** [July 31, 2023, 2:32pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/4 "2023-07-31T14:32:15Z")

</div>

Hi @denisfi, you can use to toNull() function in String Manipulation to set it to missing, whereas toEmpty will convert missing values to an empty string, as you say.

@ajisafeadeoye, if you wanted to convert value that is an empty string,_missing_ you could use:  
`toNull( $columname$ )`

or if it could be any number of spaces and you want these to become missing too:  
`toNull(strip( $columname$ ) )`

To do this across multiple columns, you could use String Manipulation (Multi column)  
`toNull(strip($$CURRENTCOLUMN$$))`,

but the big problem with using String Manipulation Multi Column is that frustratingly you cannot select columns by _Type_ (which I feel is an omission in this node), so you can’t just say you want to apply it to all the String columns. Hence you have to manually choose all the String columns, or else it will fail because the columns are of the wrong type for this function to work, and you don’t want to start using

`toNull(strip(string($$CURRENTCOLUMN$$)))`,

because then that will change all the non-string columns to string!

---

<div class="post-metadata">

**Author:** ![ajisafeadeoye](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@ajisafeadeoye](https://forum.knime.com/u/ajisafeadeoye)\
**Post date:** [July 31, 2023, 2:35pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/5 "2023-07-31T14:35:17Z")

</div>

Thank you for the time to respond

i will give a try

---

<div class="post-metadata">

**Author:** ![ajisafeadeoye](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@ajisafeadeoye](https://forum.knime.com/u/ajisafeadeoye)\
**Post date:** [July 31, 2023, 2:36pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/6 "2023-07-31T14:36:16Z")

</div>

And This is the ■■■■■■

Thank you for being detailed

Thank you for the time

Thank you

---

<div class="post-metadata">

**Author:** ![ajisafeadeoye](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@ajisafeadeoye](https://forum.knime.com/u/ajisafeadeoye)\
**Post date:** [July 31, 2023, 2:37pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/7 "2023-07-31T14:37:31Z")

</div>

Thank you for the time to respond

let me see how that goes

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [August 7, 2023, 2:38pm UTC](https://forum.knime.com/t/replacing-space-values-with-missing-value/71268/8 "2023-08-07T14:38:28Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.