Removing the leading comma and space from the string.

[dmcbrier]

I am using the Excel ( Query Editor)  which uses the power query language.

Here is my challenge.    I have a column that has data as follows:

Column Name is "Name"

Data in the Column

, David, Will, Bill

, John, Joe

David, Hillary

John, Billy

, Bill, Harry

Bill

The ones that begin with a comma how would I go about removing the leading "comma" and "space" from the string.   Thank you very much.   I do not want to remove the other commas in the middle of the string.     Thank you very much.

[Beehive]

I have followed the suggested solution below but I am receiving an error.  My issue is slightly different, I am trying to remove the characters "0-" when ever they occur at the front of values in a column.

When I try the formula with only "0" it will remove the leading zero, when I try with "0-" the words error are displayed in the column.

I would appreciate any suggestions to fix please.

[v-micsh-msft · Accepted solution]

 Hi @dmcbrier,

If all the rows contain the ", ", then we could take use of split column(By number of Characters), then delete the ", " part.

If the name column contains row that are without the ", " heading, then we could add a custom column, with the following function:

Text.TrimStart

to remove the ", " head string.

For example:

After the custom column added, you may choose to delete the original column in Advanced Editor.

Regards,

Michael

[Anonymous]

Hello, I just tried this as I have a similar situation just at the end so I used TrimEnd with the same column formula shown. Unfortunately it does not work:

What am I doing wrong?

[Anonymous]

Hi, Can we combine two values here? For example I want to trim all leading "," and ",," because I have values beginning with comma and other with two commas.

[dmcbrier]

Michael,

Thank you very much for the time and effort.   This worked absolutely brilliantly. Cheers!