# Require formula

#### Ron99

##### Active Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
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:

Code:
=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):

Code:
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

Last edited:

#### Ron99

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

#### gaz_chops

##### Well-known Member
Or maybe

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

#### Fluff

##### MrExcel MVP, Moderator
Another option
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ",",")

#### tyija1995

##### Well-known Member
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!

Last edited: