Remove empty/blank spaces in a cell

[NehaNevrekar]

Hi,

Please guide us to remove the empty/blank spaces in a cell. We are attaching the sample for your reference. 

Please note we need the same in number format and to be 15 digitis only. e.g. "157872567532303". However, the actual data that we receive contains empty spaces which cannot be even replaced in excel with the help of the replace function. e.g. "    100270021712303".

Thank you in advance.

Regards,

Neha

[DavidP]

HI @NehaNevrekar, apologies for taking a while to get back to you.

At first I was a bit stumped. but then I looked at the preview in the input data tool when loading the data from your excel file. There is a weird character at the start and end of every line, which seems to be a double byte character and escape the clutches of the data cleanse tool!

You can see this by changing the datatype from V_WString (which accepts double byte characters) to V_String (which does not).

I tried various things, but ended up going for the following  formula to get rid of the unwanted characters on the left and right.

left(Right([Customer TRN], Length([Customer TRN])-1), Length([Customer TRN])-2)

which results in:

So now, having removed the unwanted characters, you can do what we discussed before and change it to a number format if you like. Your if statement should also now work.