Require formula

[Ron99]

Hello,

I have data as mentioned below in each row.

,,,,,,,,,,,,,,,,AM,,,,,,,,,,,FE,,,,,,,BA
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,DD,,,,,,
HQ,,,,,,,BB,,,,,,,,,,,,,,,,,,,,,,,DF


The output that I require is as mentioned below.

AM,FE,BA
DD
HQ,BB,DF

Regards,
Vikas

 

[Fluff]

is
,,,,,,,,,,,,,,,,AM,,,,,,,,,,,FE,,,,,,,BA
in one cell, or is it a series of cells in one row?

Also what version of Xl are you using?

 

[tyija1995]

If it is all in 1 cell then you can perform a triple substitute to remove all additional commas except 1, as such:

E.g. A1 contains ,,,,,,,AM,,,,,,,,FE,,,,,,BA then the following formula will return:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))

Which gives ,AM,FE,BA

I appreciate this causes a comma at the start however... Using some helper cells I came up with this to clean up the other scenarios (I am sure there are more efficient ways though):

IF(RIGHT(A1,1)=CHAR(44),SUBSTITUTE(IF(LEFT(A1,1)=CHAR(44),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)),CHAR(44),"",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))),CHAR(44),"",(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)))-LEN(SUBSTITUTE(A1,CHAR(44),""))-1)),IF(LEFT(A1,1)=CHAR(44),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)),CHAR(44),"",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))))

EDIT: I suppose you could also do text to columns and join with a TEXTJOIN function, if you have that function that is, depends on the excel version

 

[Ron99]

Wow, this is great! it works exactly the way I was looking for. Thank you for your valuable time.

 

[gaz_chops]

Or maybe

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

 

[Fluff]

Another option
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ",",")

 

[tyija1995]

Wow I totally overshot this one, those formulas are way better to use than mine! Note to self: Trim is very powerful! Thanks guys

EDIT: Excel Trim is better than SQL Trim!