Excel Cell Data Clean Up - extra spaces and numbers

Hi All,

I’m using StudioX to download a CSV, copy the data into an excel and then manipulate the data as I need it. There is a column labeled Text that has data I need, but it frequently has extra spaces or numbers that I need to remove so that I can perform a VLookup with another file. I’m currently using Modify Text and Text to Left/Right to clean it up and then setting a variable with the cleaned up spacing, but it doesn’t exclude the numbers that are sometimes there and therefore my VLookup isn’t working. For Example:
1st row the data is Bank Account One XXXXX65456365635
2nd row the data is Actual Bank Account Ready 4564354 5635643
I need the output to be “Bank Account One” and “Actual Bank Account Ready”

Is there a way to do this even though each row may have different length data? One row may have 3 words another may have 6.

Thank you!!

Hey @sjavits-cohan
You can open the expression editor and use the Regex expressions.
To remove digits you can use:
System.Text.RegularExpressions.Regex.Replace(inputString, "[\d-]", String.Empty)
To remove extra spaces you can use something what remove 2 or more spaces from string:
System.Text.RegularExpressions.Regex.Replace(inputString, " {2,}", "")

1 Like

Hi @sjavits-cohan

Can you try the below

String.Format(String.Join(" ", CurrentRow("Text").ToString.Split().Where(Function(word) Not word.Any(Function(c) Char.IsDigit(c)))))

image

NewBlankTask5.zip (275.7 KB)

Cheers!!

@lrtetala this mostly worked! It got rid of all the numbers - is there a way for it to get rid of any extra spaces if there are any? For example: Bank Account.

@sjavits-cohan

Try this

String.Format(String.Join(" ", CurrentRow("Text").ToString.Split().Where(Function(word) Not word.Any(Function(c) Char.IsDigit(c))).Select(Function(word) word.Trim())))

If above not code not worked ,Could be more specific your question

@lrtetala This worked, thank you so much!

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