Excel output value changes with new output

[standbones]

Hi All,

I’m filling up data of names. The columns filled are “First Name”, “Middle Name” and “Last Name”.
There is an excel formula that I’ve created which fills up “Full Name” according to the data in the other columns.

=SUBSTITUTE(TRIM(A2&" “&B2&” “&C2&” “& IF(LEN(B2)=0,”“,” “)),” “,” ")

There are some names that don’t have middle name, so the excel formula will remove the double space.

Initial Table:

Afterwards, a new datatable will be created in another sheet and it will contain the new tabulated data. However, when I output the new datatable, it will show the “Full Name” for those who don’t have a middle name with 2 spaces. (eg. “Tommy Tan” instead of “Tommy Tan”)

Expected Result (correct)

Actual Result (wrong)

Why does the output datatable show double space? I wish to only get single space. Like how a normal name looks. Is there any solution to this?

Note that this is a sample data and my real dataset has thousands of records.

File:
testing.zip (21.7 KB)

Thanks!

[THIRU_NANI (NaNi)]

Hey!

Have you tried trim?

For example:

FirstName = “Richard”
SecondName=“Riley”

FullName = FirstName.Trim+" "+SecondName.Trim

Regards,
NaNi

[Dharunya_Devi (Dharunya Balakrishnan)]

Hi @standbones ,

Please try the below formula to get the expected result,

=TRIM(A1&" “&B1&” "&C1)

[standbones]

Hi @Dharunya_Devi and @THIRU_NANI

Appreciate the help, but I have implemented this formula as mentioned earlier.
=SUBSTITUTE(TRIM(A2&" “&B2&” “&C2&” “& IF(LEN(B2)=0,”“,” “)),” “,” ")

The formula trims the extra space, if there’s no middle name.
and the result is:

but when I read and write this data table onto another sheet / workbook, the output for “Full Name” column changes to:

As you notice, there’s a double space for the values without Middle Name. Why does this happen although the value in the first data table where I retrieve the data from does not have double space?

Thanks!

[standbones]

Hi @THIRU_NANI

I realised I made a mistake when I was using write range, I output the datatable with the old dataset…

Your solution worked!
Seems like we have to execute formula inside UiPath rather than Excel.

Thanks a lot!