How to remove all spaces from the string except newline

I am trying to read data from a table using Screen scraping. and then Converting it to a data table using Generate Data table.

I am removing spaces using this expression:

But there are still spaces left and its stored this way:

When I try to remove all spaces: Using the following expression:
This is what I get:

Is there a way to remove all spaces except Newline?



Can you try the following expression?



1 Like

Thanks for the suggestion. It works. But I need to Generate Data table from this text:

I want to use single space for column separator:

I get the same output when I write it back to Excel:


Can you share your text as a file? It’s no problem if dummy data.


HUD # Encompass Fee n Compliance Fee Description Amount Paid By Paio Encompass R Compliance R HOEPA/QM\r\n 901 Prepaid Interest Prepaid Interest 414.33 Borror L False True False",

I need 3 things here:
The First number occurrence, and the 2 consecutive Boolean values.


How about the following?

Sequence.xaml (7.5 KB)

if you want to generate datatable from the text, can you attach your text as a file? We need accurate text data.


APRFees.txt (792 Bytes)


Thank you for sharing. However, it’s difficult to identify which is column separator. For example, “Amount Paid By” probably should be separated to “Amount” and “Paid By”, but all words separated by single whitespace. (It means there is no difference between column separator and word’s space)

If we already know each column name, we might be able to create datatable from it, as workaround.


yes, that’s the whole issue here. I am reading text using screen scraping and its not accurate. and there could be many rows. Thanks for all the help.

I think the approach you shared is more reliable. I am trying to do it that way.
Last thing, can you give me regular expression for the first occurrence of number in a string?


Sometimes, we face same case scraping from pdf, web etc…

strNumber = System.Text.RegularExpressions.Regex.Match(yourString,"\d+").Value

or if there is decimal separator and/or thousand separator, the following will work.

strNumber = System.Text.RegularExpressions.Regex.Match(yourString,"[.,\d]+").Value


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