CODEWITHSUNDEEP
google-sheets
aabujamra
aabujamra

Reputation: 4636

Split transpose not considering first character as separator - any alternative?

I have this table:

enter image description here

In each of the cells there is a long list separated by ",":

Rosângela Lima, Ana Carolina Chagas, Sergio Vasquez, Rosana Corrêa, Michele Kida
Analista De Treinamento E …, A procura de nova oportunidade no mercado, Comercial na BRASILMAXI Logística Ltda., Executiva de Vendas e Solucionadora do Grupo BRASILMAXI, Executiva de Vendas na Brasilmaxi Logística LTDA
, [email protected], [email protected], [email protected], ,
@ros%C3%A2ngela-lima-4a90a324, @ana-carolina-chagas-24997022, @sergio-vasquez-25a040a4, @rosanacorrea, @michele-kida-83929230

I want it to look like this:

enter image description here

Here is the sample data

I tried to use transpose(split()) however it doesn't work the email line properly:

enter image description here

Google Sheets is not considering the first character as separator.

Upvotes: 1

Views: 35

Answers (1)

Tanaike
Tanaike

Reputation: 201513

From "MY TRY" of your shared Spreadsheet, how about modifying the formula at "C15" as follows?

From:

=TRANSPOSE(SPLIT(C3;","))

To:

=TRANSPOSE(SPLIT(C3;",";;FALSE))

  • About 4th argument, the official document says as follows.

    remove_empty_text - [ OPTIONAL - TRUE by default ] - Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cells values are added between consecutive delimiters.

Reference:

Added:

In order to retrieve the front space of each row, how about the following modified formulas?

=TRANSPOSE(SPLIT(SUBSTITUTE(C3;" ";"");",";;FALSE))

or

=TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(C3;",";;FALSE))))

Upvotes: 2

Related Questions